import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java. text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.inject.Inject;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.PrintSetup;
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.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.springframework.beans.factory.config.ConfigurableBeanFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import com.tshn.hydrology.base.BaseUtil;
import com.tshn.hydrology.base.Consts;
import com.tshn.hydrology.entity.system.Organization;
import com.tshn.hydrology.service.modules.StPptnRService;
@Controller
@RequestMapping("stPptnR")
@Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
public class StPptnRDetailsExcelExport {
@Inject
private StPptnRService stPptnRservice;
private final int ZONE_NAME_CELL_STYLE = 1;
private final int COMMON_CEELL_STYLE = 2;
private final int AVG_RAIN_CELL_STYLE = 3;
private final int NUMBERIC_CELL_STYLE = 4;
private String decimalFormatPattern = "0.0"; //
private String rainDetailExcelName = "雨晴表year.第xx期.xls"; //excel名称
private DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH");
private DateFormat chineseDateFormat = new SimpleDateFormat("yyyy年MM月dd日HH时");
private DateFormat chineseDateFormatNoYear = new SimpleDateFormat("MM月dd日HH时");
private Calendar cal = Calendar.getInstance();
private int[] queryDatePosition = {3,5}; //excel模版放置查询时间段的位置（3行5列）
private String templatePath = BaseUtil.getDefultAttPrefixPath()+Consts.STANDARD_REPORT_TEMPLATE; //excel模版位置
private int rowZoneNum =3; //每行存放的县区个数
private String[][] allAreaRainStatistics =
new String[][]{{"平均","parentAvg","AVERAGE"},{"最大","parentMax","MAX"},{"最小","parentMin","MIN"}}; //全区统计
@RequestMapping("exportRegularRainDetailsExcel")
public void exportRegularRainDetailsExcel(HttpServletRequest request,HttpServletResponse response,Model model){
String startTime = request.getParameter("startTime"); //开始查询时间
String endTime = request.getParameter("endTime"); //结束查询时间
try {
Date startDate = dateFormat.parse(startTime);
Date endDate = dateFormat.parse(endTime);
rainDetailExcelName = rainDetailExcelName.replaceAll("year", getQueryYear(startDate)); //根据查询日期重新设置excel表格名称
String queryDateSection = chineseDateFormat.format(startDate)+"~"+chineseDateFormatNoYear.format(endDate); //设置查询时间段
stPptnRservice.getStPptnRDetails(model,request);
Map<String, Object> data = model.asMap(); //降雨量信息
Workbook excelBook = getWrittenExcelbook(queryDateSection,data); //写excel
response.setContentType("application/x-msdownload"); // 通知客户文件的MIME类型：
try {
response.setHeader("Content-disposition", "attachment;filename="+new String(rainDetailExcelName.getBytes(),"ISO-8859-1"));
excelBook.write(response.getOutputStream());
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (ParseException e) {
e.printStackTrace();
}
}
/**
* 计算起始查询日期的年份
* @param startDate
* @return
*/
private String getQueryYear(Date startDate){
cal.setTime(startDate);
return String.valueOf(cal.get(Calendar.YEAR));
}
@SuppressWarnings("unchecked")
private Workbook getWrittenExcelbook(String queryDateSection,Map<String, Object> data){
int startWirteRowNum = 5;//开始行数
StringBuffer calculateArea = new StringBuffer();
Workbook workbook = null;
try {
workbook = new HSSFWorkbook(new FileInputStream(templatePath));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = workbook.getSheetAt(0);
sheet.setColumnWidth(1, 2180);
sheet.setColumnWidth(4, 2180);
sheet.setColumnWidth(7, 2180);
//在queryDatePosition 指定的位置写入查询时间
sheet.getRow(queryDatePosition[0]).getCell(queryDatePosition[1]).setCellValue(queryDateSection);
List<Map<String,Object>> stpps = (List<Map<String,Object>>)data.get("stpps");
int[] currentRowNums = new int[rowZoneNum];
for(int m=0;m<currentRowNums.length;m++){
currentRowNums[m]=startWirteRowNum;
}
for(int i=0;i<stpps.size();i++){
Map<String,Object> sttp = stpps.get(i);
String mainOrgName = String.valueOf(sttp.get("orgName"));
List<Organization> childOrgs = (List<Organization>)sttp.get("child");
int rowNum = childOrgs.size()+1; //各乡镇降雨量+县区平均降雨量
int columnZoneIndex = i%rowZoneNum;
int startRowNum = currentRowNums[columnZoneIndex];
int endRowNum = currentRowNums[columnZoneIndex]+rowNum-1;
int startAndEndColumnNum = i%rowZoneNum*rowZoneNum;
//合并县区名称单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowNum, endRowNum, startAndEndColumnNum, startAndEndColumnNum);
sheet.addMergedRegion(cellRangeAddress);
Row currentRow = null;
Cell startCalCell = null;
Cell endCalCell = null;
for(int j=0;j<rowNum;j++){
if( sheet.getRow(currentRowNums[columnZoneIndex]) == null){
currentRow = sheet.createRow(startWirteRowNum++);
currentRowNums[columnZoneIndex] = startWirteRowNum;
}else{
currentRow = sheet.getRow(currentRowNums[i%rowZoneNum]);
currentRowNums[columnZoneIndex] = currentRowNums[columnZoneIndex]+1;
}
Cell orgNameCell = currentRow.createCell(startAndEndColumnNum+1);//存放站名单元格
Cell rainCell = currentRow.createCell(startAndEndColumnNum+2);//存放降雨雨量
rainCell.setCellType(Cell.CELL_TYPE_NUMERIC);
rainCell.setCellStyle(this.getCustomedCellStyle(workbook, NUMBERIC_CELL_STYLE));
if(rowNum-1>j){
Organization org = childOrgs.get(j);
orgNameCell.setCellStyle(this.getCustomedCellStyle(workbook, COMMON_CEELL_STYLE));
orgNameCell.setCellValue(org.getOrganizationName()); //设置站名
if(j == 0){
if(!mainOrgName.equals("开发区")){
orgNameCell.setCellValue("城区");
}
startCalCell = rainCell;
}else if(j==rowNum-2){
endCalCell = rainCell;
}
rainCell.setCellValue(org.getAvgOfStpptnR()); //设置降雨量
}else{//设置县区平均降雨量
orgNameCell.setCellStyle(this.getCustomedCellStyle(workbook, AVG_RAIN_CELL_STYLE));
orgNameCell.setCellValue(mainOrgName.substring(mainOrgName.length()-1)+"平均");
rainCell.setCellValue(Double.parseDouble(String.valueOf(sttp.get("avg"))));
String excelArea = this.tranlateExcelArea(startCalCell, endCalCell); //获取excel区域
calculateArea.append(excelArea).append(",");
this.setFormula(rainCell,"AVERAGE",excelArea); //给平均雨量单元格设置公式
}
}
for (int m = cellRangeAddress.getFirstRow(); m <= cellRangeAddress.getLastRow(); m ++) {//设置表头单元格样式
CellUtil.getCell(sheet.getRow(m), startAndEndColumnNum).setCellStyle(this.getCustomedCellStyle(workbook, ZONE_NAME_CELL_STYLE));
}
Cell areaCell = CellUtil.getCell(sheet.getRow( cellRangeAddress.getFirstRow()), startAndEndColumnNum);
areaCell.setCellValue(getStringWithEnter(mainOrgName));
}
int minArrayValueIndex = 2;
int currentRowNum = currentRowNums[minArrayValueIndex] ;
int lastRowColumnIndex = minArrayValueIndex*3 ;
//合并竖行单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(currentRowNum, currentRowNum+2, lastRowColumnIndex, lastRowColumnIndex);
sheet.addMergedRegion(cellRangeAddress);
calculateArea.deleteCharAt(calculateArea.length()-1);//删除最后一个逗号
for(int k=0;k<3;k++){
if(sheet.getRow(k+currentRowNum)==null){
sheet.createRow(k+currentRowNum);
}
Row tempRow = sheet.getRow(k+currentRowNum);
Cell dataNameCell = tempRow.createCell(lastRowColumnIndex+1); //数据名称
dataNameCell.setCellStyle(this.getCustomedCellStyle(workbook, ZONE_NAME_CELL_STYLE));
dataNameCell.setCellValue(allAreaRainStatistics[k][0]);
Cell dataContentCell = tempRow.createCell(lastRowColumnIndex+2); //数据内容
dataContentCell.setCellType(Cell.CELL_TYPE_NUMERIC);//设置单元格类型
dataContentCell.setCellStyle(this.getCustomedCellStyle(workbook,NUMBERIC_CELL_STYLE));
dataContentCell.setCellValue(Double.parseDouble(String.valueOf(data.get(allAreaRainStatistics[k][1]))));
this.setFormula(dataContentCell, allAreaRainStatistics[k][2], calculateArea.toString());
}
currentRowNums[minArrayValueIndex] = currentRowNums[minArrayValueIndex] + 3;
for (int m = cellRangeAddress.getFirstRow(); m <= cellRangeAddress.getLastRow(); m ++) {//设置表头单元格样式
CellUtil.getCell(sheet.getRow(m), lastRowColumnIndex).setCellStyle(this.getCustomedCellStyle(workbook, ZONE_NAME_CELL_STYLE));
}
Cell areaCell = CellUtil.getCell(sheet.getRow( cellRangeAddress.getFirstRow()), lastRowColumnIndex);
areaCell.setCellValue("全市\r\n统计");
//动态补齐单元格
int maxArrayValueIndex = getMaxArrayValueIndex(currentRowNums);
int maxArrayValue = currentRowNums[maxArrayValueIndex];
for(int i=0;i<currentRowNums.length;i++){
if(i!=maxArrayValueIndex && maxArrayValue>currentRowNums[i]){ //如果该列单元格的行数 小于 最多行的单元格，那么动态补齐该列单元格
int startMergedRegionRow = currentRowNums[i]-1; //开始行数
int endMergedRegionRow = maxArrayValue-1; //结束行数
for(int m=0;m<3;m++){
CellRangeAddress cellRange = new CellRangeAddress(startMergedRegionRow, endMergedRegionRow, i*rowZoneNum+m, i*rowZoneNum+m);
sheet.addMergedRegion(cellRange);
for (int x = startMergedRegionRow; x <= endMergedRegionRow; x++) {
CellUtil.getCell(sheet.getRow(x), i*rowZoneNum+m).setCellStyle(
getCustomedCellStyle(workbook,i*rowZoneNum+m==0 ? ZONE_NAME_CELL_STYLE : (i*rowZoneNum+m==1 ? AVG_RAIN_CELL_STYLE : NUMBERIC_CELL_STYLE ))
);
}
}
}
}
/*--打印设置--*/
PrintSetup printSetUp = sheet.getPrintSetup();
sheet.setAutobreaks(true);
printSetUp.setFitHeight((short)1); //一页打印
printSetUp.setPaperSize(PrintSetup.A4_PAPERSIZE);//设置
sheet.setHorizontallyCenter(true);//设置打印页面为水平居中
sheet.setVerticallyCenter(true);//设置打印页面为垂直居中
return workbook;
}
/**
* 根据开始单元格和结束单元格，获取两个单元格之间的Excel区域
* @param startCalCell
* @param endCalCell
* @return
*/
private String tranlateExcelArea(Cell startCalCell,Cell endCalCell){
int startCellColumnIndex = startCalCell.getColumnIndex();//获取单元格列索引
int startCellRowIndex = startCalCell.getRowIndex(); //获取单元格行索引
int endCellColumnIndex = endCalCell.getColumnIndex();//获取单元格列索引
int endCellRowIndex = endCalCell.getRowIndex(); //获取单元格行索引
String startCellStr = BaseUtil.transformArabNoToEnglishNo(startCellColumnIndex, true)
+ (startCellRowIndex+1);
String endCellStr = BaseUtil.transformArabNoToEnglishNo(endCellColumnIndex, true)
+ (endCellRowIndex+1);
return startCellStr+":"+endCellStr;
}
private void setFormula(Cell cell,String formulaName,String calculateArea){
cell.setCellFormula(formulaName+"("+calculateArea+")");
}
/**
* 在每个字符后追加回车符
* @param inputStr
* @return
*/
private String getStringWithEnter(String inputStr){
String[] mainOrgNames = inputStr.split("");
String finalMainOrgName = "";//实现县区竖排效果
for(int x=0;x<mainOrgNames.length;x++ ){
finalMainOrgName += mainOrgNames[x] +"\r\n";
}
return finalMainOrgName;
}
private CellStyle getCustomedCellStyle(Workbook workbook,int cellStyleType){
CellStyle newCellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("仿宋_GB2312");
if(cellStyleType == ZONE_NAME_CELL_STYLE){
font.setFontName("黑体");
font.setFontHeightInPoints((short) 12);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
newCellStyle.setFont(font);
newCellStyle.setWrapText(true);
//newCellStyle.setRotation((short)-90); //字体旋转90度
}else if(cellStyleType==AVG_RAIN_CELL_STYLE){
font.setFontHeightInPoints((short) 12);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.ROYAL_BLUE.index);
newCellStyle.setFont(font);
newCellStyle.setFillBackgroundColor(HSSFColor.GREY_80_PERCENT.index);
}else if(cellStyleType == NUMBERIC_CELL_STYLE){
DataFormat format = workbook.createDataFormat();
newCellStyle.setDataFormat(format.getFormat(decimalFormatPattern));
}else{
font.setFontHeightInPoints((short) 12);
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
newCellStyle.setFont(font);
}
newCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //垂直居中
newCellStyle.setAlignment(CellStyle.ALIGN_CENTER); //水平居中
this.setBorder(newCellStyle, CellStyle.BORDER_THIN); //设置单元格边框
return newCellStyle;
}
/**
* 获取数组中最小元素对应的数组索引
* @param array
* @return
*/
private int getMaxArrayValueIndex(int[] array){
int maxValue = array[0];
int maxValueIndex = 0;
for(int i=0;i<array.length;i++){
if(maxValue<array[i]){
maxValue = array[i];
maxValueIndex = i;
}
}
return maxValueIndex;
}
/**
* 设置单元格边框宽度
* @param cellStyle
* @param borderWidth
*/
private void setBorder(CellStyle cellStyle,short borderWidth){
cellStyle.setBorderTop(borderWidth);
cellStyle.setBorderBottom(borderWidth);
cellStyle.setBorderLeft(borderWidth);
cellStyle.setBorderRight(borderWidth);
}
}
