Java读取,存写excel数据 亦凉 2022-05-10 11:22 128阅读 0赞 前言所需jar包: <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.10-FINAL</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.10-FINAL</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.1</version> </dependency> 读取excel文件内容,支持日期,数据,字符,公式,布尔 public class ExcelUtil { public static List<ExcelSheetPO> readExcel(){ List<ExcelSheetPO> sheetPOs = new ArrayList<>(); File file = new File("F:/students.xls"); try{ String fileName = file.getName(); String extName = "."+fileName.substring(fileName.lastIndexOf(".") + 1); Workbook wb = null; if(".xls".equals(extName)){ wb = new HSSFWorkbook(new FileInputStream(file)); }else if(".xlsx".equals(extName)){ wb = new XSSFWorkbook(new FileInputStream(file)); }else{ throw new IllegalArgumentException("Invalid excel version"); } //解析sheet //获取sheet页签 int sheetNum = wb.getNumberOfSheets(); for(int i = 0 ; i < sheetNum ; i ++){ Sheet sheet = wb.getSheetAt(i); Integer rowCount = sheet.getPhysicalNumberOfRows(); List<List<Object>> dataList = new ArrayList<>(); ExcelSheetPO sheetPO = new ExcelSheetPO(); sheetPO.setSheetName(sheet.getSheetName()); sheetPO.setDataList(dataList); int readRowCount = 0; if(rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()){ readRowCount = sheet.getPhysicalNumberOfRows(); }else{ readRowCount = rowCount; } //解析sheet行 for(int j = sheet.getFirstRowNum(); j < readRowCount ; j ++){ Row row = sheet.getRow(j); Integer columnCount = sheet.getRow(j).getPhysicalNumberOfCells(); if(row == null){ continue; } if(row.getFirstCellNum()< 0){ continue; } int readColumnCount = 0; if(columnCount == null || columnCount > row.getLastCellNum()){ readColumnCount = (int)row.getLastCellNum(); }else{ readColumnCount = columnCount; } List<Object> rowValue= new LinkedList<>(); for(int k = 0 ; k < readColumnCount ; k ++){ Cell cell = row.getCell(k); rowValue.add(getCellValue(wb,cell)); } System.out.println(rowValue); dataList.add(rowValue); } sheetPOs.add(sheetPO); } }catch(Exception e){ e.printStackTrace(); } return sheetPOs; } //统一处理每个数据 public static Object getCellValue(Workbook wb,Cell cell){ Object columnValue = null; if(cell !=null){ DecimalFormat df = new DecimalFormat("0");//格式化number //字符 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); //数字 DecimalFormat nf = new DecimalFormat("0.00"); switch(cell.getCellType()){ case Cell.CELL_TYPE_STRING: columnValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { columnValue = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { columnValue = nf.format(cell.getNumericCellValue()); } else { columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: columnValue = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: columnValue = ""; break; case Cell.CELL_TYPE_FORMULA: // 格式单元格 FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(cell); CellValue cellValue = evaluator.evaluate(cell); columnValue = cellValue.getNumberValue(); break; default: columnValue = cell.toString(); } } return columnValue; } } 结果如下: ![在这里插入图片描述][70] 写入磁盘操作(该方式需要指定excel模板样式,另XSSSFWorkbook与此大同小异) package util; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; 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.ss.util.CellRangeAddress; public class ExportExcel { public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){ try{ //创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); List<CellRangeAddress> cellObject = new ArrayList<>(); //创建合并单元格对象 CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,结束行,起始列,结束列 cellObject.add(callRangeAddress); CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,7); cellObject.add(callRangeAddress1); //班组与时间start CellRangeAddress callRangeAddress20 = new CellRangeAddress(2,2,0,2); cellObject.add(callRangeAddress20); CellRangeAddress callRangeAddress21 = new CellRangeAddress(2,2,3,4); cellObject.add(callRangeAddress21); CellRangeAddress callRangeAddress22 = new CellRangeAddress(2,2,5,7); cellObject.add(callRangeAddress22); //标题 CellRangeAddress callRangeAddress31 = new CellRangeAddress(3,4,0,0); cellObject.add(callRangeAddress31); CellRangeAddress callRangeAddress32 = new CellRangeAddress(3,4,1,1); cellObject.add(callRangeAddress32); CellRangeAddress callRangeAddress33 = new CellRangeAddress(3,4,2,2); cellObject.add(callRangeAddress33); CellRangeAddress callRangeAddress34 = new CellRangeAddress(3,3,3,4); cellObject.add(callRangeAddress34); CellRangeAddress callRangeAddress35 = new CellRangeAddress(3,4,5,5); cellObject.add(callRangeAddress35); CellRangeAddress callRangeAddress36 = new CellRangeAddress(3,4,6,6); cellObject.add(callRangeAddress36); CellRangeAddress callRangeAddress37 = new CellRangeAddress(3,4,7,7); cellObject.add(callRangeAddress37); //金额 CellRangeAddress callRangeAddressnumber1 = new CellRangeAddress(userList.size()+5,userList.size()+5,0,2); cellObject.add(callRangeAddressnumber1); CellRangeAddress callRangeAddressnumber2 = new CellRangeAddress(userList.size()+5,userList.size()+5,3,7); cellObject.add(callRangeAddressnumber2); //负责人 CellRangeAddress callRangeAddressPersion1 = new CellRangeAddress(userList.size()+6,userList.size()+6,0,2); cellObject.add(callRangeAddressPersion1); CellRangeAddress callRangeAddressPersion2 = new CellRangeAddress(userList.size()+6,userList.size()+6,3,4); cellObject.add(callRangeAddressPersion2); CellRangeAddress callRangeAddressPersion3 = new CellRangeAddress(userList.size()+6,userList.size()+6,5,7); cellObject.add(callRangeAddressPersion3); //说明 CellRangeAddress callRangeAddressinfo = new CellRangeAddress(userList.size()+7,userList.size()+7,0,7); cellObject.add(callRangeAddressinfo); CellRangeAddress callRangeAddressinfo1= new CellRangeAddress(userList.size()+8,userList.size()+8,0,7); cellObject.add(callRangeAddressinfo1); CellRangeAddress callRangeAddressinfo2 = new CellRangeAddress(userList.size()+9,userList.size()+9,0,7); cellObject.add(callRangeAddressinfo2); //部项目经理部 HSSFCellStyle headStyle = createCellStyle(workbook,(short)10,false,true); //派工单 HSSFCellStyle erStyle = createCellStyle(workbook,(short)13,true,true); //班组和时间 HSSFCellStyle sanStyle = createCellStyle(workbook,(short)10,false,false); //标题样式 HSSFCellStyle colStyle = createCellStyle(workbook,(short)10,true,true); //内容样式 HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true); //创建工作表 HSSFSheet sheet = workbook.createSheet("派单"); //加载合并单元格对象 for(CellRangeAddress lst:cellObject){ sheet.addMergedRegion(lst); } //设置默认宽列 sheet.setDefaultColumnWidth(15); //创建行 //创建头标题行,并且设置头标题 HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); //加载单元格样式 cell.setCellStyle(headStyle); cell.setCellValue("***项目部"); HSSFRow rower = sheet.createRow(1); HSSFCell celler = rower.createCell(0); //加载单元格样式 celler.setCellStyle(erStyle); celler.setCellValue("派工单"); HSSFRow rowsan = sheet.createRow(2); HSSFCell cellsan = rowsan.createCell(0); HSSFCell cellsan1 = rowsan.createCell(3); HSSFCell cellsan2 = rowsan.createCell(5); //加载单元格样式 cellsan.setCellStyle(sanStyle); cellsan.setCellValue("协作单位:x施工一队"); cellsan1.setCellStyle(sanStyle); cellsan1.setCellValue(""); cellsan2.setCellStyle(sanStyle); cellsan2.setCellValue("时间:2017年 10月 20日"); //创建列标题,并且设置列标题 HSSFRow row2 = sheet.createRow(3); String[] titles = {"序号","工作内容","用工总人数","工日数","","单价(元)","金额(元)","备注"}; for(int i = 0 ; i < titles.length;i ++){ HSSFCell cell2 = row2.createCell(i); //加载单元格样式 cell2.setCellStyle(colStyle); cell2.setCellValue(titles[i]); } HSSFRow rowfour = sheet.createRow(4); String[] titlefour = {"普工","技工"}; for(int i = 0 ; i < titlefour.length; i ++){ HSSFCell cell2 = rowfour.createCell(i+3); cell2.setCellStyle(colStyle); cell2.setCellValue(titlefour[i]); } //操作单元格,将用户列表写入excel if(userList != null){ int i = 1; for(int j = 0 ;j < userList.size(); j ++){ HSSFRow row3 = sheet.createRow(j+5); HSSFCell cell0 = row3.createCell(0); cell0.setCellStyle(cellStyle); cell0.setCellValue(i++); HSSFCell cell1 = row3.createCell(1); cell1.setCellStyle(cellStyle); cell1.setCellValue(userList.get(j).getWorkCtx()); HSSFCell cell2 = row3.createCell(2); cell2.setCellStyle(cellStyle); cell2.setCellValue(userList.get(j).getTotalHumanDays()); HSSFCell cell3 = row3.createCell(3); cell3.setCellStyle(cellStyle); cell3.setCellValue(userList.get(j).getGwnNum()); HSSFCell cell4 = row3.createCell(4); cell4.setCellStyle(cellStyle); cell4.setCellValue(userList.get(j).getTmnNum()); HSSFCell cell5 = row3.createCell(5); cell5.setCellStyle(cellStyle); cell5.setCellValue(userList.get(j).getTotalHumanDays()); HSSFCell cell6 = row3.createCell(6); cell6.setCellStyle(cellStyle); cell6.setCellValue(userList.get(j).getUnitAmount()); HSSFCell cell7 = row3.createCell(7); cell7.setCellStyle(cellStyle); cell7.setCellValue(userList.get(j).getUnitPrice()); } } HSSFRow rownumber = sheet.createRow(userList.size()+5); HSSFCell cellnumber = rownumber.createCell(0); HSSFCell cellnumber1 = rownumber.createCell(3); //加载单元格样式 cellnumber.setCellStyle(sanStyle); cellnumber.setCellValue("金额合计(大写"); cellnumber1.setCellStyle(sanStyle); cellnumber1.setCellValue("¥ 78 元;大写:柒拾捌元整"); HSSFRow rowpersion = sheet.createRow(userList.size()+6); HSSFCell cellpersion = rowpersion.createCell(0); HSSFCell cellpersion1 = rowpersion.createCell(3); HSSFCell cellpersion2 = rowpersion.createCell(5); //加载单元格样式 cellpersion.setCellStyle(sanStyle); cellpersion.setCellValue("协作单位负责人:"); cellpersion1.setCellStyle(sanStyle); cellpersion1.setCellValue("经办人:"); cellpersion2.setCellStyle(sanStyle); cellpersion2.setCellValue("部门负责人:"); HSSFRow rowinfo = sheet.createRow(userList.size()+7); HSSFCell cellinfo = rowinfo.createCell(0); cellinfo.setCellStyle(sanStyle); cellinfo.setCellValue("说明:1、本标工单一式两联,第一联为派工人存根,第二联用作结算"); HSSFRow rowinfo1 = sheet.createRow(userList.size()+8); HSSFCell cellinfo1 = rowinfo1.createCell(0); cellinfo1.setCellStyle(sanStyle); cellinfo1.setCellValue("2、本标工单必须在用工当日签认,否则不予认可;三日内交合同处汇总"); HSSFRow rowinfo2 = sheet.createRow(userList.size()+9); HSSFCell cellinfo2 = rowinfo2.createCell(0); cellinfo2.setCellStyle(sanStyle); cellinfo2.setCellValue("3、工日数填写精确到半个工日"); //输出 workbook.write(fout); }catch(Exception e ){ e.printStackTrace(); } } //单元格样式 private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook,short fontsize,boolean flag,boolean flag1){ HSSFCellStyle style = workbook.createCellStyle(); //是否水平居中 if(flag1){ style.setAlignment(HSSFCellStyle.ALIGN_CENTER); } style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //创建字体 HSSFFont font = workbook.createFont(); //是否加粗字体 if(flag){ font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } //加载字体 style.setFont(font); return style; } public static void main(String[] args){ //模拟部分数据 List<WorkSheetDetail> detail = new ArrayList<>(); WorkSheetDetail d1 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34"); WorkSheetDetail d2 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34"); WorkSheetDetail d3 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34"); detail.add(d1); detail.add(d2); detail.add(d3); try{ FileOutputStream fout = new FileOutputStream("F:/students.xls"); new ExportExcel().getValue(detail, fout); fout.close(); }catch(Exception e){ e.printStackTrace(); } } } package util; public class WorkSheetDetail { //工作内容 private String workCtx; //用工总人数 private Float totalHumanDays; //普工 private Integer gwnNum; //技工 private Integer tmnNum; //单价 private Float unitPrice; //金额 private Float unitAmount; //备注 private String notes; public WorkSheetDetail(String workCtx, Float totalHumanDays, Integer gwnNum, Integer tmnNum, Float unitPrice, Float unitAmount, String notes) { super(); this.workCtx = workCtx; this.totalHumanDays = totalHumanDays; this.gwnNum = gwnNum; this.tmnNum = tmnNum; this.unitPrice = unitPrice; this.unitAmount = unitAmount; this.notes = notes; } public String getWorkCtx() { return workCtx; } public void setWorkCtx(String workCtx) { this.workCtx = workCtx; } public Float getTotalHumanDays() { return totalHumanDays; } public void setTotalHumanDays(Float totalHumanDays) { this.totalHumanDays = totalHumanDays; } public Integer getGwnNum() { return gwnNum; } public void setGwnNum(Integer gwnNum) { this.gwnNum = gwnNum; } public Integer getTmnNum() { return tmnNum; } public void setTmnNum(Integer tmnNum) { this.tmnNum = tmnNum; } public Float getUnitPrice() { return unitPrice; } public void setUnitPrice(Float unitPrice) { this.unitPrice = unitPrice; } public Float getUnitAmount() { return unitAmount; } public void setUnitAmount(Float unitAmount) { this.unitAmount = unitAmount; } public String getNotes() { return notes; } public void setNotes(String notes) { this.notes = notes; } } 注:本文写入时候的数据为固定数据,实战中可以用读取的数据进行替换 ![在这里插入图片描述][70 1] 读取Excel数据返回List<Map<String,Object>>数据集 package com.servingcloud.vscloud.common.util; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * Excel 工具类 * * @author lmay.Zhou * @date 2017/10/11 17:33 * @qq 379839355 * @email Java_zlm@163.com */ @Slf4j public class ExcelUtils { /** * 是否是2003的Excel,是则true * * @param filePath 文件路径 * @return true | false */ public static boolean isExcel2003(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } /** * 是否是2007的Excel,是则true * * @param filePath 文件路径 * @return true | false */ public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 读取Excel的所有内容 * * @param fileName 文件名 * @param inputStream 文件流 * @return List<Map < String , Object>> */ public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) { List<Map<String, Object>> contents = new ArrayList<>(); //根据版本选择创建Workbook的方式 Workbook wb = null; try (InputStream is = inputStream) { if (ExcelUtils.isExcel2007(fileName)) { wb = new XSSFWorkbook(is); } else { wb = new HSSFWorkbook(is); } //得到第一个shell Sheet sheet = wb.getSheetAt(0); //得到Excel的行数 int totalRows = sheet.getPhysicalNumberOfRows(); //总列数 int totalCells = 0; //得到Excel的列数(前提是有行数),从第二行算起 if (totalRows >= 2 && sheet.getRow(1) != null) { // 从表头获取总列数 totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); } // Keys String[] keys = new String[totalCells]; //循环Excel行数,从第二行开始。标题不入库 for (int r = 0; r < totalRows; r++) { Map<String, Object> content = new HashMap<>(); Row row = sheet.getRow(r); if (row == null) { continue; } //循环Excel的列 for (int x = 0; x < totalCells; x++) { if (0 == r) { Cell title = row.getCell(x); keys[x] = title.getStringCellValue(); continue; } Cell cell = row.getCell(x); if (cell == null) { content.put(keys[x], cell); } else { //设置单元格读取为字符串读取 if (wb instanceof XSSFWorkbook) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); } if (wb instanceof HSSFWorkbook) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); } content.put(keys[x], cell); } } if (0 == r) { continue; } contents.add(content); } } catch (NullPointerException | IOException e) { log.error("导入解析异常", e); } return contents; } } 下载静态资源: public void downFile(Long fileId, HttpServletResponse response) { if (fileId == null) { return; } String fileName = null; if (BasicConstant.OUTPUT_INV_COLLECTION_TEMPLATE.equals(fileId)) { fileName = "销项导入模板.xlsx"; } if (BasicConstant.INCOME_INV_COLLECTION_TEMPLATE.equals(fileId)) { fileName = "进项导入模板.xlsx"; } OutputStream output = null; InputStream in = null; try { in = getClass().getClassLoader().getResourceAsStream("file/"+fileName); output = response.getOutputStream(); response.reset(); response.setHeader("Content-disposition", "attachment; filename=" + UriEncoder.encode(fileName)); response.setContentType("application/msexcel"); byte[] buff = new byte[1024]; int rc = 0; while ((rc = in.read(buff, 0, 100)) > 0) { response.getOutputStream().write(buff, 0, rc); } output.flush(); } catch (IOException e) { log.error("文件上传失败",e); } finally { try { if(output!=null) { output.close(); } if(in!=null) { in.close(); } } catch (IOException e) { log.error("文件上传失败",e); } } **注:如果特殊的Excel表格,可以使用Freemarker,先写好【文件名.ftl】模板,然后再导出** [70]: /images/20220510/71ef6c6453c0415198dbc728b03772f9.png [70 1]: /images/20220510/704a1ea4adf24507877764df3c1a88e8.png
还没有评论,来说两句吧...