POI学习记录

做为一个搞编程开发的朋友,千万不要告诉朋友亲戚,你是干这个的.

不然你就会遇到很多这样的问题(当然我们都很乐于助人的,可爱的程序员):

我的电脑怎么没有声音了,又或者是我的电脑怎么没有显示了,又或者是下面这样的.

需求:

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