Java之按行、列读excel笔记
来源：互联网 发布：dota2战绩查询软件 编辑：程序博客网 时间：2024/05/22 03:21
原文
首先jar包地址：https://pan.baidu.com/s/1hrPilta
或者自己去poi官网去下载。
核心工具类
package com.beforeship.excel;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil{ //默认单元格内容为数字时格式 private static DecimalFormat df = new DecimalFormat("0"); // 默认单元格格式化日期字符串 private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 格式化数字 private static DecimalFormat nf = new DecimalFormat("0.00"); public static ArrayList<ArrayList<Object>> readExcel(final File file, final Integer onesheet) { if (file == null) { return null; } if (file.getName().endsWith("xlsx")) { //处理ecxel2007 return readExcel2007(file, onesheet); } else { //处理ecxel2003 return readExcel2003(file, onesheet); } } /* * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似 lists.get(0).get(0)表示过去Excel中0行0列单元格 */ public static ArrayList<ArrayList<Object>> readExcel2003(final File file, final Integer onesheet) { try { final ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>(); ArrayList<Object> colList; final HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); final HSSFSheet sheet = wb.getSheetAt(onesheet); HSSFRow row; HSSFCell cell; Object value; for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); colList = new ArrayList<Object>(); if (row == null) { //当读取行为空时 if (i != sheet.getPhysicalNumberOfRows()) {//判断是否是最后一行 rowList.add(colList); } continue; } else { rowCount++; } for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { //当该单元格为空 if (j != row.getLastCellNum()) {//判断是否是该行中最后一个单元格 colList.add(""); } continue; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: //System.out.println(i + "行" + j + " 列 is String type"); value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } //System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString()); break; case XSSFCell.CELL_TYPE_BOOLEAN: // System.out.println(i + "行" + j + " 列 is Boolean type"); value = Boolean.valueOf(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: // System.out.println(i + "行" + j + " 列 is Blank type"); value = ""; break; default: // System.out.println(i + "行" + j + " 列 is default type"); value = cell.toString(); }// end switch colList.add(value); } //end for j rowList.add(colList); } //end for i return rowList; } catch (final Exception e) { return null; } } public static ArrayList<ArrayList<Object>> readExcel2007(final File file, final Integer onesheet) { try { final ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>(); ArrayList<Object> colList; final XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); final XSSFSheet sheet = wb.getSheetAt(onesheet); XSSFRow row; XSSFCell cell; Object value; for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); colList = new ArrayList<Object>(); if (row == null) { //当读取行为空时 if (i != sheet.getPhysicalNumberOfRows()) {//判断是否是最后一行 rowList.add(colList); } continue; } else { rowCount++; } for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { //当该单元格为空 if (j != row.getLastCellNum()) {//判断是否是该行中最后一个单元格 colList.add(""); } continue; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: //System.out.println(i + "行" + j + " 列 is String type"); value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } //System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString()); break; case XSSFCell.CELL_TYPE_BOOLEAN: //System.out.println(i + "行" + j + " 列 is Boolean type"); value = Boolean.valueOf(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: // System.out.println(i + "行" + j + " 列 is Blank type"); value = ""; break; default: //System.out.println(i + "行" + j + " 列 is default type"); value = cell.toString(); }// end switch colList.add(value); } //end for j rowList.add(colList); } //end for i return rowList; } catch (final Exception e) { //System.out.println("exception"); return null; } } public static void writeExcel(final ArrayList<ArrayList<Object>> result, final String path) { if (result == null) { return; } final HSSFWorkbook wb = new HSSFWorkbook(); final HSSFSheet sheet = wb.createSheet("sheet1"); for (int i = 0; i < result.size(); i++) { final HSSFRow row = sheet.createRow(i); if (result.get(i) != null) { for (int j = 0; j < result.get(i).size(); j++) { final HSSFCell cell = row.createCell(j); cell.setCellValue(result.get(i).get(j).toString()); } } } final ByteArrayOutputStream os = new ByteArrayOutputStream(); try { wb.write(os); } catch (final IOException e) { e.printStackTrace(); } final byte[] content = os.toByteArray(); final File file = new File(path);//Excel文件生成后存储的位置。 OutputStream fos = null; try { fos = new FileOutputStream(file); fos.write(content); os.close(); fos.close(); } catch (final Exception e) { e.printStackTrace(); } } public static DecimalFormat getDf() { return df; } public static void setDf(final DecimalFormat df) { ExcelUtil.df = df; } public static SimpleDateFormat getSdf() { return sdf; } public static void setSdf(final SimpleDateFormat sdf) { ExcelUtil.sdf = sdf; } public static DecimalFormat getNf() { return nf; } public static void setNf(final DecimalFormat nf) { ExcelUtil.nf = nf; }}调用工具，按行读取、返回数据
public static ArrayList<String> getHeadOfThird(final String filePath) throws Exception { final File file = new File(filePath); final ArrayList<String> data2 = new ArrayList<>(); final ArrayList<String> data3 = new ArrayList<>(); final Integer sheet = 3; final ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file, sheet); for (int j = 0; j < result.get(1).size(); j++) { data2.add(result.get(1).get(j).toString()); } for (int j = 0; j < result.get(2).size(); j++) { data3.add(result.get(2).get(j).toString()); } //System.out.println("头部第三行：" + data2); return data3; }按列读取、返回数据(行读取比较好读，按列相对麻烦些)
public static RequireOptionPreferred getRequireOption(final String filePath) throws Exception { final File file = new File(filePath); final ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file, 2); final ArrayList<String> requireWord = new ArrayList<>(); final ArrayList<String> optionalWord = new ArrayList<>(); final ArrayList<String> preferredWord = new ArrayList<>(); for (int i = 0; i < result.size(); i++) { for (int j = 0; j < result.get(i).size(); j++) { final String tmpRequired = "Required"; if (tmpRequired.equals(result.get(i).get(j).toString())) { requireWord.add(result.get(i).get(1).toString()); } final String tmpOptional = "Optional"; if (tmpOptional.equals(result.get(i).get(j).toString())) { optionalWord.add(result.get(i).get(1).toString()); } final String tmpPreferred = "Preferred"; if (tmpPreferred.equals(result.get(i).get(j).toString())) { preferredWord.add(result.get(i).get(1).toString()); } } } final RequireOptionPreferred requireOptionPreferred = new RequireOptionPreferred(); requireOptionPreferred.setOptionalWord(optionalWord); requireOptionPreferred.setOptionalWord(optionalWord); requireOptionPreferred.setPreferredWord(preferredWord); //System.out.println(requireOptionPreferred); return requireOptionPreferred; }readExcel里面传入两个参数：前面的是excel路径
final File file = new File(filePath);
后面是读取excel中哪一个工作空间，第一个sheet为0，第二个为1...以此类推。
作者： tangjunfeng
链接：http://www.imooc.com/article/16914
来源：慕课网
本文原创发布于慕课网 ，转载请注明出处，谢谢合作！
0 0
- Java之按行、列读excel笔记
- java导出excel，自定义列与行
- EXCEL之读取列数据
- java 导入excel案列
- 按行或列读取excel文档
- excel冻结行或列
- EXCEL 行、列限制数
- 用JAVA编写Excel列编号
- java导出excel合并行跟列
- java实现导出Excel(跨行，跨列)
- Java+Excel学习笔记
- EXCEL功能之Excel标题跨列居中
- jxl用即用java处理excel之读excel
- 【NCRE】——Excel之如何获取行高和列宽
- excel行列互换excel行复制成列或者是excel列复制成行
- EXCEL奇数行（列）偶数行（列）操作
- DataGridView按列顺序导出Excel
- EXCEL中使用VB按列排序
- Q88：全局光照（Global Illumination）——Path Tracing算法生成反射焦散效果的图形
- 静态变量-动态变量
- Junit单元测试
- 【方法】机器学习中的数据清洗与特征处理
- React学习之State与生命周期基友情（四）
- Java之按行、列读excel笔记
- Sqoop学习笔记 --- python保存 Sqoop 打印的信息
- ruby on rails 撤销取消操作
- 数据库关系运算
- 在MFC中手动添加消息处理函数PreTranslateMessage
- 斐波纳契数列
- HDU 3348 coins 贪心 最少/最多硬币问题
- OpenGL纹理详解（上）
- 路由时间
