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;
}
}