POI学习记录
来源：互联网 发布：红孩儿大话火焰山 知乎 编辑：程序博客网 时间：2024/05/21 00:15
做为一个搞编程开发的朋友,千万不要告诉朋友亲戚,你是干这个的.
不然你就会遇到很多这样的问题(当然我们都很乐于助人的,可爱的程序员):
我的电脑怎么没有声音了,又或者是我的电脑怎么没有显示了,又或者是下面这样的.
需求:
a.统计excel表中,每个sheet总,每个项目的费时
a.1:每个sheet中,可能有不同的项目,或者相同的项目
a.2:每个sheet中的项目,又分为不同类型的项目(不同类型,主要区别于格式不同,然后去判断是否有重复时得按,sheet,然后类型来区分)
b.将统计的结果,按不同类型的格式,产生一个"汇总"sheet
代码(POI操作包括,上下合并单元格,左右合并单元格,设置单元格基本格式,写入公式简单操作,做个备份记录学习用):
package com.deal;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.HashMap;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.Set;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;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.ss.usermodel.WorkbookFactory;import org.apache.poi.ss.util.CellRangeAddress;/** * 读取excel2007文档 * * @author Administrator */@SuppressWarnings("serial")public class ReadXlsX { /** 日期 总 项目*/ private static Map<String,String> part1 = new LinkedHashMap<String, String>(){ { put("日期","日期"); put("总","总"); put("项目","项目"); } }; /**设计项目前面那几个*/ private static Map<String,String> part2 = new LinkedHashMap<String, String>(); /**设计项目*/ private static Map<String,String> part3 = new LinkedHashMap<String, String>(); /**销售配合*/ private static Map<String,String> part4 = new LinkedHashMap<String, String>(); /**施工配合*/ private static Map<String,String> part5 = new LinkedHashMap<String, String>(); /**最后面那几个*/ private static Map<String,String> part6 = new LinkedHashMap<String, String>(); /** 用户式耗时 Map<sheetName(即人名),Map<部分标识,Map<项目名,Map<阶段名,加总列行号,及列号>>>>*/ private static Map<String,Map<Integer,Map<String,Map<String,int[]>>>> personTime = new HashMap<String, Map<Integer,Map<String,Map<String,int[]>>>>(); private static List<String> sheetNameList = new ArrayList<String>(); private static String[] columnNumChar = null; private static List<String> item = new ArrayList<String>(){ { add("方案前期");add("方案");add("初步设计");add("施工图设计");add("投标配合"); } }; private static int employNum = 0; public String dealXlsX(String filePath) { try { this.copy(filePath); FileInputStream input = new FileInputStream(filePath); Workbook xlsFile = WorkbookFactory.create(input); int sheetNum = xlsFile.getNumberOfSheets(); if(this.isHaveTotalSheet(xlsFile, sheetNum-1)){ sheetNum -=1; xlsFile.removeSheetAt(sheetNum); } employNum =sheetNum; for (int i = 0; i < sheetNum; i++) { Sheet sheet = xlsFile.getSheetAt(i); int rowNum = sheet.getPhysicalNumberOfRows(); String titleIndexStr = this.getCellValue(sheet,0,null,0,rowNum-1,null); sheetNameList.add(sheet.getSheetName());// System.out.println(sheet.getSheetName()+ ":"+titleIndexStr); if(!titleIndexStr.equals("")){ String[] indexTemp = titleIndexStr.split(","); int[] index = new int[indexTemp.length]; for (int j = 0; j < indexTemp.length; j++) { index[j] = Integer.parseInt(indexTemp[j]); } //得到第二部分3-->index[0];日期后面的 this.getCellValue(sheet, 1, part2, 2, rowNum-1,new int[]{3,index[0]}); //得到第三部分index[0]-->index[1] 设计项目 this.getCellValue(sheet, 1, part3, 3, rowNum-1,new int[]{index[0],index[1]}); //得到第四部分i1 -->i2 销售配合 this.getCellValue(sheet, 1, part4, 4, rowNum-1,new int[]{index[1],index[2]}); //得到第五部分i2-->第一个增加项 施工配合 String index5 = this.getCellValue(sheet, 1, part5, 5, rowNum-1,new int[]{index[2],0}); //得到第六部分:第一个增加项-->末尾 末尾 this.getCellValue(sheet, 1, part6, 6, rowNum-1,new int[]{Integer.parseInt(index5)+1,0}); } } Sheet totalSheet = this.createSheet(xlsFile, "汇总"); totalSheet.setForceFormulaRecalculation(true); this.writeCell(totalSheet); xlsFile.setActiveSheet(xlsFile.getNumberOfSheets()-1); FileOutputStream fileOut = new FileOutputStream(filePath); xlsFile.write(fileOut); input.close(); fileOut.close(); return "0"; } catch (Exception e) { e.printStackTrace(); return e.getMessage(); } } public static void main(String[] args){ String filePath = "D:\\aa.xlsx"; new ReadXlsX().dealXlsX(filePath); } //////////////////////////////////REDA FUNCTION//////////////////////////////////////////////////////////// public String getCellValue(Sheet sheet,int rowIndex,Map<String,String> content,int part,int totalRow,int... cellIndex){ StringBuilder indexStr = new StringBuilder(); Row row = sheet.getRow(rowIndex); if(row != null){ int cellNum = row.getPhysicalNumberOfCells(); int i = 0; if(cellIndex != null){ i = cellIndex[0]; //start point //end point if(part <5){ cellNum = cellIndex[1]; } } for (; i < cellNum; i++) { Cell cell = row.getCell(i); if(cell != null && !cell.toString().equals("")){ if(part == 0){ indexStr.append(i+","); }else{ int[] rowAndColumn = new int[]{totalRow,i}; this.putPersonIntoMap(sheet.getSheetName(), cell.toString(), part, rowAndColumn); if(!content.containsKey(cell.toString())){ content.put(cell.toString(), cell.toString()); } if(part == 5){ if(cell.toString().equals("增加项")){ return indexStr.append(i).toString(); } } } } } } return indexStr.toString(); } /** 用户式耗时 Map<sheetName(即人名),Map<部分标识,Map<项目名,Map<阶段名,加总列行号,及列号>>>>*/ public void putPersonIntoMap(String personName,String projectName,int part,int[] rowAndColunm){ String proName = "N/A"; //阶段名称 if(part == 3 || part == 4){ for (int i = 0; i < item.size(); i++) { proName = item.get(i); this.putDataToMap(personName, projectName, proName, part, rowAndColunm[0],rowAndColunm[1]); rowAndColunm[1] +=1; } }else{ this.putDataToMap(personName, projectName, proName, part, rowAndColunm[0],rowAndColunm[1]); } } /** 用户式耗时 Map<sheetName(即人名),Map<部分标识,Map<项目名,Map<阶段名,加总列行号,及列号>>>>*/// private static Map<String,Map<Integer,Map<String,Map<String,int[]>>>> public void putDataToMap(String personName,String projectName,String proName,int part,int row,int column){ int[] rowAndColunm = new int[]{row,column}; if(personTime.containsKey(personName)){ Map<Integer,Map<String,Map<String,int[]>>> personMap= personTime.get(personName); if(personMap.containsKey(part)){ Map<String,Map<String,int[]>> partMap = personMap.get(part); if(partMap.containsKey(projectName)){ Map<String,int[]> projectAllPro = partMap.get(projectName); projectAllPro.put(proName, rowAndColunm); }else{ Map<String, int[]> proMap = new HashMap<String, int[]>(); proMap.put(proName, rowAndColunm); partMap.put(projectName, proMap); } }else{ Map<String, int[]> proMap = new HashMap<String, int[]>(); proMap.put(proName, rowAndColunm); Map<String,Map<String, int[]>> projectMap = new HashMap<String, Map<String,int[]>>(); projectMap.put(projectName, proMap); personMap.put(part, projectMap); } }else{ Map<String, int[]> proMap = new HashMap<String, int[]>(); proMap.put(proName, rowAndColunm); Map<String,Map<String, int[]>> projectMap = new HashMap<String, Map<String,int[]>>(); projectMap.put(projectName, proMap); Map<Integer,Map<String,Map<String,int[]>>> partMap= new HashMap<Integer, Map<String,Map<String,int[]>>>(); partMap.put(part, projectMap); personTime.put(personName,partMap); }// this.printTime(); } //////////////////////////////////WRITE FUNCTION/////////////////////////////////////////////////////////// /** * 创建汇总表 * @param xlsFile * @param sheeName * @return */ public Sheet createSheet(Workbook xlsFile,String sheeName){ Sheet newSheet = xlsFile.createSheet(sheeName); newSheet.autoSizeColumn(1); int rowNum = employNum+7; int colNum = part1.size()+part2.size()+(part3.size()*5) +(part4.size()*5)+(part5.size())+part6.size();// System.out.println("总列数"+colNum); CellStyle style = xlsFile.createCellStyle(); Font font = xlsFile.createFont(); font.setFontHeightInPoints((short) 10); //设置为10号字体 style.setWrapText(true); //自动换行 style.setFont(font); style.setBorderTop(CellStyle.BORDER_DOTTED); //实线BORDER_THIN 虚线BORDER_DOTTED style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setAlignment(CellStyle.ALIGN_CENTER); //文体居中对齐 for (int i = 0; i < rowNum; i++) { Row newRow = newSheet.createRow(i); for (int j = 0; j < colNum; j++) { Cell cell = newRow.createCell(j); cell.setCellStyle(style); if(j <=2){ newSheet.setColumnWidth(j, 1000); //设置列宽 }else{ newSheet.setColumnWidth(j, 900); //设置列宽 } } } return newSheet; } public void writeCell(Sheet sheet){ int startPoint=part1.size()+part2.size(); int endPoint1 = startPoint+(part3.size()*5); //设计项目 int endPoint2 = endPoint1+(part4.size()*5); //销售配合 int endPoint3 = endPoint2+(part5.size()); //施工配合// System.out.println("分界点:"+startPoint+","+endPoint1+","+endPoint2+","+endPoint3); int[] point = new int[]{startPoint,endPoint1,endPoint2,endPoint3}; this.writeTitle(sheet, point); this.writeEmployName(sheet); this.writePart1(sheet); this.writePart2(sheet); this.writeItemS(sheet, startPoint,part3,3); this.writeItemS(sheet, endPoint1, part4,4); this.writePart5(sheet, endPoint2, part5); this.writePart6(sheet, endPoint3, part6); //写入公式示例// this.writeCellValue(sheet, "xxxx", 6, 0, true);// this.writeCellValue(sheet, "A7", 3, 2, false); } public void writeTitle(Sheet sheet,int[] point){ Row titleRow = sheet.getRow(0); //先合并单元格,再写 this.mergeCell(sheet, 0, 0, point[0], point[1]-1); titleRow.getCell(point[0]).setCellValue("设计项目");// this.mergeCell(sheet, 0, 0, point[1], point[2]-1); titleRow.getCell(point[1]).setCellValue("销售配合");// this.mergeCell(sheet, 0, 0, point[2], point[3]-1); titleRow.getCell(point[2]).setCellValue("施工配合"); } /** * 将第一部分内容(日期,总,项目 固定的三个值)写入第二列 * @param sheet */ public void writePart1(Sheet sheet){ List<String> part1List = this.mapKeyToList(part1); Row secRow = sheet.getRow(1); for (int i = 0; i < part1.size(); i++) { secRow.getCell(i).setCellValue(part1List.get(i)); } } public void writePart2(Sheet sheet){ List<String> part2List = this.mapKeyToList(part2); int size = part2List.size(); int z =0; for (int i = 0; i < size; i++) { String value = part2List.get(i); this.mergeCell(sheet, 1, 2, 3+i, 3+i); if(value.equals("增加项")){ continue; } this.writeCellValue(sheet, value, 1, z+3,true); this.writePersonFor(sheet, 2, value, "N/A", z+3); z++; } this.writeCellValue(sheet, "增加项", 1, z+3,true); this.writePersonFor(sheet, 2, "增加项", "N/A", z+3); } /** * 写设计项目与销售配合部分的第一个方法. * 循环得到要写的项目,如果为增加项,则跳过. * 否则写入该项目. * 写完所有项目之后,添加一下增加项 * @param sheet * @param startPoint * @param dataMap */ public void writeItemS(Sheet sheet,int startPoint,Map<String,String> dataMap,int part){ List<String> partList = this.mapKeyToList(dataMap); for (int i = 0; i < partList.size(); i++) { String value = partList.get(i); if(!value.equals("增加项")){ int endPoint = startPoint+4; this.writeItem(sheet, 1, 1, startPoint, endPoint, value,part); startPoint = endPoint+1; }else{ continue; } } this.writeItem(sheet, 1, 1, startPoint, startPoint+4, "增加项",part); } /** * 写设计项目与销售配合部分的主体方法. * 先合并第2行横着的5列,写入值 * 然后再第3行的依次写入5个固定的值. * @param sheet * @param startRow * @param endRow * @param startColumn * @param endColumn * @param value */ public void writeItem(Sheet sheet,int startRow,int endRow,int startColumn,int endColumn,String value,int part){ this.mergeCell(sheet, startRow, endRow, startColumn,endColumn); this.writeCellValue(sheet, value, startRow, startColumn,true); for (int i = 0; i < 5; i++) { this.writeCellValue(sheet, item.get(i), startRow+1, startColumn+i,true); this.writePersonFor(sheet, part, value, item.get(i), startColumn+i); } } /** * 写施工配合部分 * @param sheet * @param startPoint * @param dataMap */ public void writePart5(Sheet sheet,int startPoint,Map<String,String> dataMap){ List<String> part5List = this.mapKeyToList(dataMap); for (String string : part5List) { System.out.println(string); } for (int i = 0; i < part5List.size(); i++) { this.write2RowItem(sheet, 1, 1+1, startPoint+i, part5List.get(i),5); }// if(part5List.size() == 1){ // }else{// this.writeItemS(sheet, startPoint, dataMap,5);// } } /** * 将表格的最后一部写进去 * @param sheet * @param startPoint * @param dataMap */ public void writePart6(Sheet sheet,int startPoint,Map<String,String> dataMap){ List<String> part6List = this.mapKeyToList(dataMap); for (int i = 0; i < part6List.size(); i++) { this.write2RowItem(sheet, 1, 1+1, startPoint+i, part6List.get(i),6); } } /** * 合并竖着的两行,然后再写入字符串 * @param sheet * @param startRow * @param endRow * @param startColumn * @param value */ public void write2RowItem(Sheet sheet,int startRow,int endRow,int startColumn,String value,int part){ this.mergeCell(sheet, startRow, endRow, startColumn, startColumn); this.writeCellValue(sheet, value, startRow, startColumn,true); this.writePersonFor(sheet, part, value, "N/A", startColumn); } /** * 向单元格写入值 * @param sheet 操作的sheet * @param value 写的值 * @param row 行号(第一行为0) * @param column 列号 * @param flag 是否为公式 true:为String,false:公式 */ public void writeCellValue(Sheet sheet,String value,int row,int column,boolean flag){ Row rowD = sheet.getRow(row); if(flag){ rowD.getCell(column).setCellValue(value); }else{ rowD.getCell(column).setCellType(Cell.CELL_TYPE_FORMULA); rowD.getCell(column).setCellFormula(value); } } /** * 合并单元格 * @param sheet 操作的sheet * @param sRow 开始行号(第一行为0) * @param endRow 结束行号(第一行为0) * @param sCol 开始列号(第一列为0) * @param endCol 结束列号(第一列为0) */ public void mergeCell(Sheet sheet,int sRow,int endRow,int sCol,int endCol){ sheet.addMergedRegion(new CellRangeAddress(sRow,endRow,sCol,endCol)); } public void writeEmployName(Sheet sheet){ for (int i = 0; i < sheetNameList.size(); i++) { this.writeCellValue(sheet, sheetNameList.get(i), i+3, 1,true); this.writeCellValue(sheet, "总", i+3, 0, true); } this.writeCellValue(sheet, "项目总用时", sheetNameList.size()+3, 1, true); this.writeCellValue(sheet, "设计师用时", sheetNameList.size()+5, 1, true); this.writeCellValue(sheet, "设计助理用时", sheetNameList.size()+6, 1, true); sheet.setColumnWidth(1, 1500); } /** 用户式耗时 Map<sheetName(即人名),Map<部分标识,Map<项目名,Map<阶段名,加总列行号,及列号>>>>*/ public void writePersonFor(Sheet sheet,int part,String projectName,String proName,int column){ if(columnNumChar == null){ columnNumChar = this.initColumnChar(sheet); } for (int i = 0; i < sheetNameList.size(); i++) {// Row row = sheet.getRow(arg0) int writeRowNum = i+3; Row writeRow = sheet.getRow(writeRowNum); String personName = writeRow.getCell(1).toString(); //得到人名 Map<String,Map<String,int[]>> partProjectMap = personTime.get(personName).get(part); String value = "N/A";// System.out.println(columnNumChar.length+"--->"+personName+"--->"+projectName+"-->"+proName); if(partProjectMap.containsKey(projectName)){ int[] srcCell = partProjectMap.get(projectName).get(proName);// System.out.println(columnNumChar.length+"--->"+personName+"--->"+projectName+"-->"+proName+"--->"+srcCell[0]+","+srcCell[1]); value = personName+"!"+columnNumChar[srcCell[1]]+""+(srcCell[0]+1); this.writeCellValue(sheet, value, writeRowNum, column, false); }else{ this.writeCellValue(sheet, value, writeRowNum, column, true); } } //计算项目总用时: int totalRow = 3+sheetNameList.size()+1; String value="SUM("+columnNumChar[column]+"4"+":"+columnNumChar[column]+(totalRow-1)+")"; this.writeCellValue(sheet, value, totalRow-1, column, false); } ///////////////////////////////////////UTIL FUNCTION/////////////////////////////////////////////////////// public static void testPrint(String str,Map<String,String> map){ Set<String> key = map.keySet(); for (String item : key) { System.out.print(item+","); }// System.out.println(str+map.size()); System.out.println(); System.out.println(); } /** * 返回由Map所有的key转换为的List * @param partMap * @return */ public List<String> mapKeyToList(Map<String,String> partMap){ List<String> reList = new ArrayList<String>(); Set<String> keys = partMap.keySet(); for (String item : keys) { reList.add(item); } return reList; } public boolean isHaveTotalSheet(Workbook xlsFile,int index){ if(xlsFile.getSheetAt(index).getSheetName().equals("汇总")){ return true; } return false; } public String[] initColumnChar(Sheet sheet){ int maxColnum = sheet.getRow(1).getPhysicalNumberOfCells();// System.out.println("第二列长度为"+maxColnum); String[] columnChar = new String[maxColnum]; String[] baseChar = new String[]{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}; for (int i = 0; i < columnChar.length; i++) { if(i>=26){ columnChar[i] = baseChar[i/26-1]+baseChar[i%26]; }else{ columnChar[i] = baseChar[i%26]; } } return columnChar; } public void printTime(){ Set<String> names = personTime.keySet(); for (String name : names) { Set<Integer> parts = personTime.get(name).keySet(); for (Integer part : parts) { Set<String> projects = personTime.get(name).get(part).keySet(); for (String project : projects) { Set<String> pros = personTime.get(name).get(part).get(project).keySet(); for (String pro : pros) { int[] cell = personTime.get(name).get(part).get(project).get(pro); System.out.println(name+"--->"+part+"--->"+project+"--->"+pro+cell[0]+","+cell[1]); } } } } System.out.println(); System.out.println(); } public void copy(String file) throws IOException { FileInputStream fin = new FileInputStream(new File(file)); String newFileName = file.substring(0,file.lastIndexOf("."))+"bak"+file.substring(file.lastIndexOf(".")); FileOutputStream fout = new FileOutputStream(new File(newFileName)); int bytesRead; byte[] buf = new byte[4 * 1024]; // 4K while ((bytesRead = fin.read(buf)) != -1) { fout.write(buf, 0, bytesRead); } fout.flush(); fout.close(); fin.close(); } }代码及excel表格:http://download.csdn.net/detail/ilyzj/4542743
- POI学习记录
- POI学习
- POI学习
- 百万记录poi导出日记
- 百万记录poi导出日记
- POI学习笔记
- POI学习总结1
- apache POI 学习笔记
- poi操作Excel学习
- 学习POI处理word
- poi（转载）学习
- poi学习之一
- POI初步学习1
- POI的简单学习
- poi导入时报错问题记录
- 记录防止忘记poi导出excel
- POI处理超过65536条记录
- 记录一下poi导入excel的问题
- 优秀设计师应当知道的20大UI设计原则
- C/C++中的计时函数
- 即時 3D 繪圖的陰影效果
- 组合问题
- 更改fstab文件出现 错误
- POI学习记录
- 年月日三下拉框联动
- C++关键字static的用法
- Math函数性能比较
- git log
- 一个仿照Nginx的内存池
- sdfsdfasd
- Hibernate Class Is Not Mapped ？
- 想你，但不爱你
