package com.sprucetec.tms.controller.fee.export;
import com.sprucetec.tms.controller.base.BaseFeeExportExcel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Arrays;
import java.util.List;
/**
* 基础运费导出excel
* yangweiqiang
* 2016.7.19
*/
public class BasicDistributeFeeExportExcel extends BaseFeeExportExcel {
public BasicDistributeFeeExportExcel(String title, String[] rowName, List<Object[]> dataList, HttpServletResponse response, HttpServletRequest request, String selectedCityName, String month) {
super(title, rowName, dataList, response, request, selectedCityName, month);
}
/*
* 实现基础运费的导出逻辑
*/
@Override
public void export() throws Exception {
//设置excel的表头
Workbook workbook = setExcelTitle();
HSSFSheet sheet = (HSSFSheet) workbook.getSheet(title);
HSSFCellStyle style = getStyle(workbook);
//将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
Object[] obj = dataList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i + 3);//创建所需的行数
for (int j = 0; j < obj.length; j++) {
HSSFCell cell = null;   //设置单元格的数据类型
if (j == 0) {
cell = row.createCell(j);
cell.setCellValue(String.valueOf(i + 1));
} else {
cell = row.createCell(j);
if (!"".equals(obj[j]) && obj[j] != null) {
if (j == 10) {
int rownum = row.getRowNum() + 1;
String formula = "SUM(L" + rownum + ",O" + rownum + ",P" + rownum + ",Q" + rownum + ",R" + rownum + ",S" + rownum + ",W" + rownum + ")-SUM(T" + rownum + ",U" + rownum + ",V" + rownum + ")";
cell.setCellFormula(formula);
}
if (j == 11 || (14 <= j && j <= 22)) {
cell.setCellValue(Double.parseDouble(obj[j].toString()));                        //设置单元格的值
} else {
cell.setCellValue(obj[j].toString());
}
} else {
cell.setCellValue("");
}
}
cell.setCellStyle(style);                                    //设置单元格样式
}
}
//让列宽随着导出的列长自动适应
setSheetColumnWidthAutoResize(sheet, rowName.length);
//添加数据校验
CellRangeAddress address1 = new CellRangeAddress(3, sheet.getLastRowNum(), 10, 11);
CellRangeAddress address2 = new CellRangeAddress(3, sheet.getLastRowNum(), 14, 22);
addSheetValidationByNumberData(sheet, Arrays.asList(address1, address2), 0, 10000);
//设置二位小数
HSSFDataFormat dataFormat = (HSSFDataFormat) workbook.createDataFormat();
short formatStyle = dataFormat.getFormat("0.00");
int totalRows = dataList.size();
for (int i = 0; i < totalRows; i++) {
HSSFRow row = sheet.getRow(i + 3);
for (int j = 0; j < rowName.length; j++) {
if (j == 11 || (j >= 14 && j <= 22)) {
HSSFCell cell = row.getCell(j);
HSSFCellStyle tmpStyle = cell.getCellStyle();
tmpStyle.setDataFormat(formatStyle);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
}
}
}
//do Export
doExport(workbook);
}
}