Java poi读取excel文件_Java批量导入excel数据
程序员文章站
2022-02-22 08:20:24
...
这应该是一个比较全的示例了,更加复杂的功能可以在此基础上扩展。此示例基于apache的POI类库,相关jar包就不列举了。这个类库很通用,网上很好找。我是用maven的方式加载这个jia的。
maven
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
ExcelUtil.java
package com.dwtedx.income.utility; 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(File file){ if(file == null){ return null; } if(file.getName().endsWith("xlsx")){ //处理ecxel2007 return readExcel2007(file); }else{ //处理ecxel2003 return readExcel2003(file); } } /* * @return 将返回结果存储在ArrayList内,存储结构与二位数组类似 * lists.get(0).get(0)表示过去Excel中0行0列单元格 */ public static ArrayList<ArrayList<Object>> readExcel2003(File file){ try{ ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>(); ArrayList<Object> colList; HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet sheet = wb.getSheetAt(0); 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(Exception e){ return null; } } public static ArrayList<ArrayList<Object>> readExcel2007(File file){ try{ ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>(); ArrayList<Object> colList; XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet sheet = wb.getSheetAt(0); 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(Exception e){ System.out.println("exception"); return null; } } public static void writeExcel(ArrayList<ArrayList<Object>> result,String path){ if(result == null){ return; } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet1"); for(int i = 0 ;i < result.size() ; i ){ HSSFRow row = sheet.createRow(i); if(result.get(i) != null){ for(int j = 0; j < result.get(i).size() ; j ){ HSSFCell cell = row.createCell(j); cell.setCellValue(result.get(i).get(j).toString()); } } } ByteArrayOutputStream os = new ByteArrayOutputStream(); try { wb.write(os); } catch (IOException e){ e.printStackTrace(); } byte[] content = os.toByteArray(); File file = new File(path);//Excel文件生成后存储的位置。 OutputStream fos = null; try { fos = new FileOutputStream(file); fos.write(content); os.close(); fos.close(); }catch (Exception e){ e.printStackTrace(); } } public static DecimalFormat getDf() { return df; } public static void setDf(DecimalFormat df) { ExcelUtil.df = df; } public static SimpleDateFormat getSdf() { return sdf; } public static void setSdf(SimpleDateFormat sdf) { ExcelUtil.sdf = sdf; } public static DecimalFormat getNf() { return nf; } public static void setNf(DecimalFormat nf) { ExcelUtil.nf = nf; } }
调用方法
import java.io.File; import java.util.ArrayList; public class Main { public static void main(String[] args) { File file = new File("F:/excel/c.xlsx"); ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file); for(int i = 0 ;i < result.size() ;i ){ for(int j = 0;j<result.get(i).size(); j ){ System.out.println(i "行 " j "列 " result.get(i).get(j).toString()); } } } }
以上是部分代码,更多代码可以下载源代码查看,下面是Demo的下载地址。
java poi读取excel文件源代码下载链接: java读取excel数据 密码: ingc。
若资源对你有帮助,浏览后有很大收获,不妨小额打赏我一下,你的鼓励是维持我不断写博客最大动力。
想获取DD博客最新代码,你可以扫描下方的二维码,关注DD博客微信公众号(ddblogs)。
或者你也可以关注我的新浪微博,了解DD博客的最新动态:DD博客官方微博(dwtedx的微博)。
如对资源有任何疑问或觉得仍然有很大的改善空间,可以对该博文进行评论,希望不吝赐教。
为保证及时回复,可以使用博客留言板给我留言: DD博客留言板(dwtedx的留言板)。
感谢你的访问,祝你生活愉快、工作顺心,欢迎常来逛逛。