1 packagectais.business.gzkp.common;2 importjava.io.File;3 importjava.io.FileInputStream;4 importjava.text.SimpleDateFormat;5 importjava.util.Date;6
7 importorg.apache.poi2.hssf.usermodel.HSSFCell;8 importorg.apache.poi2.hssf.usermodel.HSSFCellStyle;9 importorg.apache.poi2.hssf.usermodel.HSSFFont;10 importorg.apache.poi2.hssf.usermodel.HSSFRow;11 importorg.apache.poi2.hssf.usermodel.HSSFSheet;12 importorg.apache.poi2.hssf.usermodel.HSSFWorkbook;13
14 importctais.business.dashboard.service.ExportExcel;15
16 importctais.config.Config;17 importctais.services.data.DataWindow;18 importctais.services.xml.XMLDataObject;19 importctais.services.xml.XMLParser;20 importctais.util.StringEx;21 importjxl.Workbook;22 importjxl.format.Alignment;23 importjxl.write.Label;24 importjxl.write.WritableCellFormat;25 importjxl.write.WritableFont;26 importjxl.write.WritableSheet;27 importjxl.write.WritableWorkbook;28
29
30 /**
31 *
Title: 生成EXCEL文件
32 *Description: 转换String字符串为EXCEL文档
38 */39
40 public classCreateExcel {41 private final static String CONFIG_FILE_PATH =Config.CTAIS_HOME;42 WritableWorkbook wwb = null;43 XMLDataObject xdo = null;44
45 publicCreateExcel(){46
47 }48
49 /**
50 * 生成EXCEL51 *@paramsql 查询SQL52 *@paramczryDm 操作人员代码53 *@paramtitles 导出列标题54 *@paramexlTitle excel表头55 *@return
56 *@throwsException57 */
58 public String newToExcel(String sql,String czryDm,String[] titles,String exlTitle) throwsException59 {60 try{61 HSSFWorkbook wb = newHSSFWorkbook();62 HSSFSheet sheet =wb.createSheet();63 ExportExcel exportExcel = newExportExcel(wb, sheet);64 StringBuffer sffer = newStringBuffer();65 //int colNum = 30;
66
67 DataWindow dw =DataWindow.dynamicCreate(sql.toString());68 dw.setConnectionName(Icomm.GZKPJNDI);69 long dwRet =dw.retrieve();70 if (dwRet <= 0) {71 sffer.append("nodata");72 returnsffer.toString();73 } else{74 sffer.append("");75 }76
77 int colNum =dw.getColumnCount();78
79 //给工作表列定义列宽(实际应用自己更改列数)
80 for (short i = 0; i <= colNum; i++) {81 sheet.setColumnWidth(i, (short) 4000);82 }83 //创建单元格样式
84 HSSFCellStyle cellHeadStyle =wb.createCellStyle();85 //指定单元格居中对齐
86 cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);87 //指定单元格垂直居中对齐
88 cellHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);89 //指定当单元格内容显示不下时自动换行
90 cellHeadStyle.setWrapText(true);91 //设置单元格字体
92 HSSFFont headFont =wb.createFont();93 headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);94 headFont.setFontName("宋体");95 headFont.setFontHeight((short) 200);96 cellHeadStyle.setFont(headFont);97
98 //创建报表头部
99 Date dt=newDate();100 SimpleDateFormat sdt=new SimpleDateFormat("yyyyMMddhhmmssS");101 String sfm = czryDm + "_" +sdt.format(dt);102
103 //设置列头
104 exportExcel.createNormalHead(exlTitle, colNum-1);105 HSSFRow row1 = sheet.createRow(1);106
107 for(int i = 0; i < titles.length; i ++) {108 HSSFCell cell = row1.createCell((short)i);109 cell.setEncoding(HSSFCell.ENCODING_UTF_16);110 cell.setCellStyle(cellHeadStyle);111 cell.setCellValue(titles[i]);112 }113 Object value = "";114
115 //设置表格样式
116 HSSFCellStyle cellStyle =wb.createCellStyle();117 //指定单元格居中对齐
118 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);119 //指定单元格垂直居中对齐
120 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);121 //指定当单元格内容显示不下时自动换行
122 cellStyle.setWrapText(true);123 //设置单元格字体
124 HSSFFont font =wb.createFont();125 font.setBoldweight(HSSFFont.SS_NONE);126 font.setFontName("宋体");127 font.setFontHeight((short) 200);128 cellStyle.setFont(font);129 for(int i = 0 ; i < dw.getRowCount(); i++) {130 HSSFRow row = sheet.createRow(i + 2);131 for(int j = 1; j <= dw.getColumnCount(); j++) {132 HSSFCell cell = row.createCell((short)(j-1));133 cell.setEncoding(HSSFCell.ENCODING_UTF_16);134 cell.setCellStyle(cellStyle);135 value = dw.getItemAny(i, j-1);136 if(value == null) {137 cell.setCellValue("");138 } else{139 cell.setCellValue(value.toString());140 }141 }142 }143 //设置导出路径,此处需要注意如果是Linux系统需要手动建路径,(此处的原因有人比较清楚的话还请指教)引用新建的文件路径
144 String path = "/export/";145 File file = newFile(path);146 if(!file.exists()) {147 file.mkdirs();148 }149
150 String fileName = sfm+".xls";151
152 //String pth = path.trim() + File.separator + fileName;
153 String pth = path.trim() +fileName;154 pth =pth.trim();155
156 String outPutInfo =exportExcel.outputExcel(pth);157
158 sffer.append("" + sfm + "");159 sffer.append("" + path + "");160 sffer.append("" + fileName + "");161 sffer.append("" + outPutInfo + "");162
163 returnsffer.toString();164 }165 catch(Exception e) {166 e.printStackTrace();167 throw newException(e.getMessage());168 }169 }170
171 }