public String excelDocumentMonthly(List<Map> list,HttpServletRequest request ) throws Exception {
String[] ColumnT={"序号","年月","数量","各类别数量","收发类别"};
String serverRealPath = this.getServerRealPath(request); // 工程路径
String outputFile=serverRealPath+Config.getParam("gwjh.templatedownpath")+"dayin.xls";
//String outputFile="D://dayin.xls";
try{
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
HSSFSheet sheet = workbook.createSheet();
HSSFFont font = workbook.createFont();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN );//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN );//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN );//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//显示列名
for(int i=0;i<ColumnT.length;i++){
HSSFRow rowT = sheet.createRow((short)0);
HSSFCell cellT = rowT.createCell((short)i );
sheet.setColumnWidth((short)i,(short)5000);
rowT.setHeight((short)500);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellT.setCellStyle(style);
cellT.setCellType(HSSFCell.CELL_TYPE_STRING);
cellT.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cellT.setCellValue(ColumnT[i]);
}
for(int w=1 ; w<=list.size() ; w++){
Map curren = list.get(w-1);
if(w%2!=0){
//显示序号
HSSFRow row= sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
HSSFCell cell = row.createCell((short)0 );
sheet.addMergedRegion(new Region((short)w , (short) 0, (short)(w+1), (short)0));
//设置行高列宽
sheet.setColumnWidth((short)w,(short)5000);
row.setHeight((short)800);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("INDEX").toString());
//显示年份
row = sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
cell = row.createCell((short)1 );
sheet.addMergedRegion(new Region((short)w , (short) 1, (short)(w+1), (short)1));
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)500);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
// cell.setCellValue(curren.get("TIMEMONTH").toString());
cell.setCellValue( curren.get("TIMEMONTH").toString().substring(0,4)+'-'+curren.get("TIMEMONTH").toString().substring(4));
//显示总数
cell = row.createCell((short)2 );
sheet.addMergedRegion(new Region((short)w , (short) 2, (short)(w+1), (short)2));
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)500);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("RSCOUNT").toString());
cell = row.createCell((short)3 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("COUNT").toString());
cell = row.createCell((short)4 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("RSTYPE").toString());
}else{
int t=w-1;
HSSFRow row = sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
HSSFCell cell = row.createCell((short)0 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)0);
cell.setCellStyle(style);
cell.setCellValue("");
row = sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
cell = row.createCell((short)1 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)0);
cell.setCellStyle(style);
cell.setCellValue("");
row = sheet.createRow((short)w);
cell = row.createCell((short)2 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)0);
cell.setCellStyle(style);
cell.setCellValue("");
row= sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
cell = row.createCell((short)3 );
sheet.setColumnWidth((short)w,(short)5000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("COUNT").toString());
cell = row.createCell((short)4 );
sheet.setColumnWidth((short)w,(short)5000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("RSTYPE").toString());
}
}
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束,关闭文件
fOut.close();
System.out.println("文件生成...");
return Config.getParam("gwjh.templatedownpath")+"dayin.xls";
}catch(Exception e) {
System.out.println("已运行 xlCreate() : " + e );
return "0";
}
}
String[] ColumnT={"序号","年月","数量","各类别数量","收发类别"};
String serverRealPath = this.getServerRealPath(request); // 工程路径
String outputFile=serverRealPath+Config.getParam("gwjh.templatedownpath")+"dayin.xls";
//String outputFile="D://dayin.xls";
try{
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
HSSFSheet sheet = workbook.createSheet();
HSSFFont font = workbook.createFont();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN );//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN );//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN );//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//显示列名
for(int i=0;i<ColumnT.length;i++){
HSSFRow rowT = sheet.createRow((short)0);
HSSFCell cellT = rowT.createCell((short)i );
sheet.setColumnWidth((short)i,(short)5000);
rowT.setHeight((short)500);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellT.setCellStyle(style);
cellT.setCellType(HSSFCell.CELL_TYPE_STRING);
cellT.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cellT.setCellValue(ColumnT[i]);
}
for(int w=1 ; w<=list.size() ; w++){
Map curren = list.get(w-1);
if(w%2!=0){
//显示序号
HSSFRow row= sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
HSSFCell cell = row.createCell((short)0 );
sheet.addMergedRegion(new Region((short)w , (short) 0, (short)(w+1), (short)0));
//设置行高列宽
sheet.setColumnWidth((short)w,(short)5000);
row.setHeight((short)800);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("INDEX").toString());
//显示年份
row = sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
cell = row.createCell((short)1 );
sheet.addMergedRegion(new Region((short)w , (short) 1, (short)(w+1), (short)1));
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)500);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
// cell.setCellValue(curren.get("TIMEMONTH").toString());
cell.setCellValue( curren.get("TIMEMONTH").toString().substring(0,4)+'-'+curren.get("TIMEMONTH").toString().substring(4));
//显示总数
cell = row.createCell((short)2 );
sheet.addMergedRegion(new Region((short)w , (short) 2, (short)(w+1), (short)2));
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)500);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("RSCOUNT").toString());
cell = row.createCell((short)3 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("COUNT").toString());
cell = row.createCell((short)4 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("RSTYPE").toString());
}else{
int t=w-1;
HSSFRow row = sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
HSSFCell cell = row.createCell((short)0 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)0);
cell.setCellStyle(style);
cell.setCellValue("");
row = sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
cell = row.createCell((short)1 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)0);
cell.setCellStyle(style);
cell.setCellValue("");
row = sheet.createRow((short)w);
cell = row.createCell((short)2 );
sheet.setColumnWidth((short)w,(short)3000);
row.setHeight((short)0);
cell.setCellStyle(style);
cell.setCellValue("");
row= sheet.createRow((short)w);//在索引0的位置创建单元格(左上端)
cell = row.createCell((short)3 );
sheet.setColumnWidth((short)w,(short)5000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("COUNT").toString());
cell = row.createCell((short)4 );
sheet.setColumnWidth((short)w,(short)5000);
row.setHeight((short)400);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cell.setCellStyle(style);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(curren.get("RSTYPE").toString());
}
}
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束,关闭文件
fOut.close();
System.out.println("文件生成...");
return Config.getParam("gwjh.templatedownpath")+"dayin.xls";
}catch(Exception e) {
System.out.println("已运行 xlCreate() : " + e );
return "0";
}
}