java导出Excel合并单元格

最新推荐文章于 2024-07-12 13:37:03 发布

伊见钟卿

最新推荐文章于 2024-07-12 13:37:03 发布

阅读量1.7k

点赞数

本文链接：https://blog.csdn.net/TylorMin/article/details/84844395

版权

package com.xinhua.xpm.settle.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.druid.util.StringUtils;

/**
* @author minqiang
* @version 创建时间 2018年10月18日下午10:33:57
*/
public class ReadExcelUtil {

   public static List<List<String>> readExcel(File excelFile) throws IOException {
       List<List<String>> datas = new ArrayList<List<String>>();
       InputStream in = new FileInputStream(excelFile);
       Workbook workbook = null;
       if (excelFile.getName().toLowerCase().endsWith("xlsx")) {
           workbook = new XSSFWorkbook(in);
       } else {
           workbook = new HSSFWorkbook(in);
       }
       Sheet sheet = workbook.getSheetAt(0);
       int[] cellNum = {3,4,5,12,13};
       for (Row row : sheet) {
           int rowNum = row.getRowNum();
           if (rowNum == 0) { //第一行标题跳过
               continue;
           }
           //如果第一行中第四列合同号出现空值，跳出循环
           String cellValue = getCellData(row.getCell(cellNum[0]));
           if (StringUtils.isEmpty(cellValue)) {
               break;
           }
           List<String> data = new ArrayList<String>();
           for (int num = 0; num < cellNum.length; num++) {
               Cell cell = row.getCell(cellNum[num]);
               String cellData = getCellData(cell);
               data.add(cellData);
           }
           datas.add(data);
       }
       return datas;
   }

   private static String getCellData(Cell cell) {
       String cellData = "";
       switch (cell.getCellType()) {
       case HSSFCell.CELL_TYPE_NUMERIC: // 数字保留两位小数
           cellData = new DecimalFormat("#.##").format(cell.getNumericCellValue());
           break;
       case HSSFCell.CELL_TYPE_STRING: // 字符串
           cellData = cell.getRichStringCellValue().toString();
           break;
       case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
           cellData = String.valueOf(cell.getBooleanCellValue());
           break;
       case HSSFCell.CELL_TYPE_FORMULA: // 公式
           cellData = cell.getCellFormula().toString();
           break;
       default:
           break;
       }
       return cellData;

   }

   public static HSSFWorkbook getHSSFWorkbook(String sheetName, Map<String,List<String>> settleData, String createDate, String createUser, String prjName, String contractNo, String settleType){

   // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
          HSSFWorkbook wb = new HSSFWorkbook();
   // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
   HSSFSheet sheet = wb.createSheet(sheetName);
   //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列，截至列
   sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));
   sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));
   sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 8));
   sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 12));
   sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 15));
   sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 2));
   sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 5));
   sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 8));
   sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 11));
   sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 14));
   sheet.addMergedRegion(new CellRangeAddress(2, 2, 15, 17));
   // 第三步，创建sheet的第一行并定义样式
   HSSFCellStyle style = wb.createCellStyle();
   style.setAlignment(HorizontalAlignment.CENTER);
   style.setVerticalAlignment(VerticalAlignment.CENTER);
   HSSFFont font = wb.createFont();
   font.setFontName("黑体");
   font.setFontHeightInPoints((short) 16);//设置字体大小
   style.setFont(font);

   HSSFRow row = sheet.createRow(0);
   row.setHeight((short) (25 * 20));
   HSSFCell cell = row.createCell(0);
   cell.setCellValue(prjName + "项目费用明细");
   cell.setCellStyle(style);
   // 第四步，创建列样式
   HSSFCellStyle hSSFCellStyle = wb.createCellStyle();
   HSSFFont hssfFont = wb.createFont();
   hssfFont.setFontName("黑体");
   hssfFont.setFontHeightInPoints((short) 12);//设置字体大小
   hSSFCellStyle.setFont(hssfFont);

   //第五步，创建第二行，记录统计时间，统计人
   switch (settleType) {
           case "1":
               settleType = "销售结算";
               break;
           case "2":
               settleType = "工程结算";
               break;
           case "3":
               settleType = "项目结算";
               break;
           }
   HSSFRow secondRow = sheet.createRow(1);
   HSSFCell firstCell = secondRow.createCell(0);//第一列
   HSSFCell secondCell = secondRow.createCell(1);//第二列
   HSSFCell sixCell = secondRow.createCell(6);//第七列
   HSSFCell sevenCell = secondRow.createCell(7); //第八列
   HSSFCell nineCell = secondRow.createCell(9); //第十列
   HSSFCell tenCell = secondRow.createCell(10); //第十一列
   HSSFCell thirteenCell = secondRow.createCell(13); //第十四列
   HSSFCell fourteenCell = secondRow.createCell(14); //第十五列
   HSSFCell secondRowSixteenCell = secondRow.createCell(16); //第十七列
   HSSFCell seventeenCell = secondRow.createCell(17); //第十八列
   firstCell.setCellValue("项目名称");
   secondCell.setCellValue(prjName);
   sixCell.setCellValue("结算类型");
   sevenCell.setCellValue(settleType);
   nineCell.setCellValue("合同号");
   tenCell.setCellValue(contractNo);
   thirteenCell.setCellValue("统计时间");
   fourteenCell.setCellValue(createDate);
   secondRowSixteenCell.setCellValue("统计人");
   seventeenCell.setCellValue(createUser);
   secondRow.setHeight((short) (20 * 15));
   //第六步，创建第三行
   HSSFCellStyle cellStyle = wb.createCellStyle();
   cellStyle.setAlignment(HorizontalAlignment.CENTER);
   cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
   cellStyle.setFont(hssfFont);

   Object[] titleArr = settleData.keySet().toArray();
   HSSFRow thirdRow = sheet.createRow(2);
   thirdRow.setHeight((short) (20 * 15));
   HSSFCell thirdRowSecondCell = thirdRow.createCell(0);
   thirdRowSecondCell.setCellValue(titleArr[0] + "(万元)");
   thirdRowSecondCell.setCellStyle(cellStyle);
   HSSFCell thirdRowThirdCell = thirdRow.createCell(3);
   thirdRowThirdCell.setCellValue(titleArr[1] + "(万元)");
   thirdRowThirdCell.setCellStyle(cellStyle);
   HSSFCell thirdRowFourCell = thirdRow.createCell(6);
   thirdRowFourCell.setCellValue(titleArr[2] + "(万元)");
   thirdRowFourCell.setCellStyle(cellStyle);
   HSSFCell thirdRowFiveCell = thirdRow.createCell(9);
   thirdRowFiveCell.setCellValue(titleArr[3] + "(万元)");
   thirdRowFiveCell.setCellStyle(cellStyle);
   HSSFCell thirdRowSixCell = thirdRow.createCell(12);
   thirdRowSixCell.setCellValue(titleArr[4] + "(万元)");
   thirdRowSixCell.setCellStyle(cellStyle);
   HSSFCell sixteenCell = thirdRow.createCell(15);
   sixteenCell.setCellValue("合计(万元)");
   sixteenCell.setCellStyle(cellStyle);

   //第七步，创建第四行，并赋值
   HSSFRow fourRow = sheet.createRow(3);
   fourRow.setHeight((short) (20 * 15));
   HSSFCell fourRowFirstCell = fourRow.createCell(0);
   fourRowFirstCell.setCellValue("预算");
   HSSFCell fourRowSecondCell = fourRow.createCell(1);
   fourRowSecondCell.setCellValue("实际");
   HSSFCell fourRowThirdCell = fourRow.createCell(2);
   fourRowThirdCell.setCellValue("执行");
   HSSFCell fourRowFourtCell = fourRow.createCell(3);
   fourRowFourtCell.setCellValue("预算");
   HSSFCell fourRowFiveCell = fourRow.createCell(4);
   fourRowFiveCell.setCellValue("实际");
   HSSFCell fourRowSixCell = fourRow.createCell(5);
   fourRowSixCell.setCellValue("执行");
   HSSFCell fourRowSevenCell = fourRow.createCell(6);
   fourRowSevenCell.setCellValue("预算");
   HSSFCell fourRowEightCell = fourRow.createCell(7);
   fourRowEightCell.setCellValue("实际");
   HSSFCell fourRowNineCell = fourRow.createCell(8);
   fourRowNineCell.setCellValue("执行");
   HSSFCell fourRowTenCell = fourRow.createCell(9);
   fourRowTenCell.setCellValue("预算");
   HSSFCell fourRowElevenCell = fourRow.createCell(10);
   fourRowElevenCell.setCellValue("实际");
   HSSFCell fourRowTwelveCell = fourRow.createCell(11);
   fourRowTwelveCell.setCellValue("执行");
   HSSFCell fourRowThirteenCell = fourRow.createCell(12);
   fourRowThirteenCell.setCellValue("预算");
   HSSFCell fourRowFourteenCell = fourRow.createCell(13);
   fourRowFourteenCell.setCellValue("实际");
   HSSFCell fourRowfifteenCell = fourRow.createCell(14);
   fourRowfifteenCell.setCellValue("执行");
   HSSFCell fourRowSixteenCell = fourRow.createCell(15);
   fourRowSixteenCell.setCellValue("预算");
   HSSFCell fourRowSeventeenCell = fourRow.createCell(16);
   fourRowSeventeenCell.setCellValue("实际");
   HSSFCell fourRowEighteenCell = fourRow.createCell(17);
   fourRowEighteenCell.setCellValue("执行");

   //第八步，创建第五行
   HSSFRow fiveRow = sheet.createRow(4);
   fiveRow.setHeight((short) (20 * 15));
   HSSFCell fiveRowFirstCell = fiveRow.createCell(0);
   fiveRowFirstCell.setCellValue("test");
   int count = 0;
   BigDecimal budgetTotalCost = new BigDecimal("0");
   BigDecimal actualTotalCost = new BigDecimal("0");
   BigDecimal executTotalCost = new BigDecimal("0");
   for (List<String> datas : settleData.values()) {
       for (int i = 0; i < datas.size(); i++) {
           //计算预算、实际、执行总费用
           switch (i) {
                       case 0:
                           budgetTotalCost = budgetTotalCost.add(new BigDecimal(datas.get(i)));
                           break;
                       case 1:
                           actualTotalCost = actualTotalCost.add(new BigDecimal(datas.get(i)));
                           break;
                       case 2:
                           executTotalCost = executTotalCost.add(new BigDecimal(datas.get(i)));
                           break;
                   }
           HSSFCell fiveRowCell = fiveRow.createCell(count);
           fiveRowCell.setCellValue(datas.get(i));
           count++;
       }
   }
   //创建合计列下的‘预算’,‘实际’,‘执行’列并赋值
   HSSFCell fiveRowSixteenCell = fiveRow.createCell(15);
   fiveRowSixteenCell.setCellValue(budgetTotalCost.toString());
   HSSFCell fiveRowSeventeenCell = fiveRow.createCell(16);
   fiveRowSeventeenCell.setCellValue(actualTotalCost.toString());
   HSSFCell fiveRowEighteenCell = fiveRow.createCell(17);
   fiveRowEighteenCell.setCellValue(executTotalCost.toString());
   return wb;
   }
}