sheetToExcel.java
1 package tree; 2 import java.io.BufferedInputStream; 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileOutputStream; 6 import java.io.IOException; 7 import java.io.InputStream; 8 import java.io.OutputStream; 9 import java.sql.Connection; 10 import java.sql.ResultSet; 11 import java.sql.SQLException; 12 import java.sql.Statement; 13 import java.util.ArrayList; 14 import java.util.HashMap; 15 import java.util.Map; 16 17 import javax.servlet.ServletException; 18 import javax.servlet.http.HttpServlet; 19 import javax.servlet.http.HttpServletRequest; 20 import javax.servlet.http.HttpServletResponse; 21 22 import net.sf.jxls.transformer.XLSTransformer; 23 import net.sf.jxls.*; 24 25 import org.apache.commons.beanutils.RowSetDynaClass; 26 import org.apache.poi.hssf.usermodel.*; 27 import org.apache.poi.hssf.util.Region; 28 import org.apache.poi.poifs.filesystem.POIFSFileSystem; 29 30 import tree.SheetFunc; 31 @SuppressWarnings({ "serial", "unused" }) 32 public class sheetsToExcel extends HttpServlet { 33 34 public sheetsToExcel() { 35 super(); 36 } 37 public void doGet(HttpServletRequest request, HttpServletResponse response) 38 throws ServletException, IOException { 39 } 40 public void destroy() { 41 super.destroy(); // Just puts "destroy" string in log 42 } 43 44 public void doPost(HttpServletRequest request, HttpServletResponse response) 45 throws ServletException, IOException { 46 Connection conn = new connectionDataBase().connProc(); 47 Statement stmt; 48 Connection connnum = new connectionDataBase().connProc(); 49 Statement stmtnum; 50 try { 51 String sqltext =java.net.URLDecoder.decode(request.getParameter("SQL"), "UTF-8"); 52 sqltext=sqltext.toLowerCase(); 53 String BSRQ_ =java.net.URLDecoder.decode(request.getParameter("BSRQ"), "UTF-8"); //20111227 54 String BBRQ_ =java.net.URLDecoder.decode(request.getParameter("BBRQ"), "UTF-8"); 55 String PERROW=java.net.URLDecoder.decode(request.getParameter("PERROW"), "UTF-8"); 56 String templateFileName =java.net.URLDecoder.decode(request.getParameter("templateFileName"), "UTF-8"); 57 String destFileName = java.net.URLDecoder.decode(request.getParameter("destFileName"), "UTF-8"); 58 String BSRQ=BSRQ_.substring(0,4)+"年"+BSRQ_.substring(4, 6)+"月"+BSRQ_.substring(6,8)+"日"; 59 String BBRQ=BBRQ_.substring(0,4)+"年"+BBRQ_.substring(4, 6)+"月"; 60 int index=sqltext.indexOf("from"); 61 String sqlCount="select count(*) as NO "+sqltext.substring(index); 62 63 stmt = conn.createStatement(); 64 ResultSet rs = stmt.executeQuery(sqltext); 65 66 stmtnum = connnum.createStatement(); 67 ResultSet rsnum = stmtnum.executeQuery(sqlCount); 68 int dataRow=0; 69 while(rsnum.next()) 70 { 71 dataRow=rsnum.getInt("NO");//获取查询的记录数 72 } 73 int headRow=7;//表头行数 74 int perRow=Integer.parseInt(PERROW);;//每页输出数据行 75 int addSheetNum=dataRow/perRow;//需要增加的页数 76 String totalSheetNum=String.valueOf(addSheetNum+1); 77 int lastSheetRow=dataRow%perRow;//最后一页数据行 78 79 int[][] sqlDataInt; 80 sqlDataInt=new int[dataRow][5]; 81 82 float[][] sqlDataFloat; 83 sqlDataFloat=new float[dataRow][5]; 84 85 String[][] sqlDataString; 86 sqlDataString=new String[dataRow][3]; 87 for(int m=0;rs.next();m++) 88 { 89 sqlDataInt[m][0]=rs.getInt("JCMSL"); 90 sqlDataInt[m][1]=rs.getInt("CCSL"); 91 sqlDataInt[m][2]=rs.getInt("CCL"); 92 sqlDataInt[m][3]=rs.getInt("GHL"); 93 sqlDataInt[m][4]=rs.getInt("JCL"); 94 95 sqlDataFloat[m][0]=rs.getFloat("YD"); 96 sqlDataFloat[m][1]=rs.getFloat("YDZJE"); 97 sqlDataFloat[m][2]=rs.getFloat("KD"); 98 sqlDataFloat[m][3]=rs.getFloat("KDZJE"); 99 sqlDataFloat[m][4]=rs.getFloat("SPJE"); 100 101 sqlDataString[m][0]=rs.getString("KB"); 102 sqlDataString[m][1]=rs.getString("BYHLJ"); 103 sqlDataString[m][2]=rs.getString("BZ"); 104 } 105 RowSetDynaClass rsdc = new RowSetDynaClass(rs, false); 106 int i = rsdc.getRows().size(); 107 Map beans = new HashMap(); 108 //beans.put( "HCDC", rsdc.getRows()); 109 beans.put("BSRQ",BSRQ); // 110 beans.put("BBRQ",BBRQ); // 111 112 InputStream is = new FileInputStream(templateFileName); 113 XLSTransformer transformer = new XLSTransformer(); 114 transformer.transformXLS( templateFileName, beans,destFileName); 115 HSSFWorkbook workBook = transformer.transformXLS(is, beans); 116 117 for(int num=0;num<addSheetNum;num++)//新增sheet 118 { 119 String numStr = String.valueOf(num+2); 120 workBook.createSheet("第"+numStr+"页"); 121 SheetFunc.copyRows(workBook, 0, num+1,0 , headRow-1, 0);//复制表头 122 workBook.getSheetAt(num+1).setColumnWidth((short)0, (short)8192);//256,31.38 123 } 124 /* 125 * 创建数据区格式和字体*/ 126 HSSFCellStyle cellStyle = workBook.createCellStyle(); 127 HSSFFont font=workBook.createFont(); 128 font.setFontName("楷体_GB2312"); 129 font.setFontHeightInPoints((short)11); 130 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 131 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 132 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 133 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 134 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); 135 cellStyle.setFont(font); 136 /* 137 * 创建下栏区格式和字体*/ 138 HSSFCellStyle foterCellStyle = workBook.createCellStyle(); 139 HSSFFont foterFont=workBook.createFont(); 140 foterFont.setFontName("楷体_GB2312"); 141 foterFont.setFontHeightInPoints((short)12); 142 foterCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); 143 foterCellStyle.setFont(foterFont); 144 145 for(int num=0;num<addSheetNum;num++) 146 { 147 HSSFSheet sheet = workBook.getSheetAt(num); 148 String numStr = String.valueOf(num+1); 149 for(short r=0;r<perRow;r++)// 150 { 151 HSSFRow row=sheet.createRow(headRow+r);// 152 int temp=num*perRow+r;//减少循环中的计算量 153 for(short c=0;c<2;c++) 154 { 155 HSSFCell cell = row.createCell(c);// 156 SheetFunc.createCellString(workBook, row, c, sqlDataString[temp][c],cellStyle); 157 } 158 for(short c=2;c<7;c++) 159 { 160 HSSFCell cell = row.createCell(c);// 161 if(sqlDataInt[num*perRow+r][c-2] ==0) 162 { 163 SheetFunc.createCellString(workBook, row, c, " ",cellStyle); 164 }else{ 165 SheetFunc.createCellInt(workBook, row, c,sqlDataInt[temp][c-2],cellStyle); 166 } 167 } 168 169 for(short c=7;c<12;c++) 170 { 171 HSSFCell cell = row.createCell(c);// 172 if(sqlDataFloat[num*perRow+r][c-7] == 0f) 173 { 174 SheetFunc.createCellString(workBook, row, c, " ",cellStyle); 175 }else{ 176 SheetFunc.createCellFloat(workBook, row, c, sqlDataFloat[temp][c-7], cellStyle); 177 } 178 } 179 180 for(short c=12;c<13;c++) 181 { 182 HSSFCell cell = row.createCell(c);// 183 SheetFunc.createCellString(workBook, row, c, sqlDataString[temp][c-10],cellStyle); 184 } 185 } 186 for(int j=0;j<perRow;j=j+2)//合并行 187 { 188 sheet.addMergedRegion(new Region(7+j,(short)0,8+j,(short)0)); 189 } 190 191 for(short r=0;r<1;r++)// 192 { 193 HSSFRow row=sheet.createRow(headRow+perRow+r);// 194 HSSFCell cell = row.createCell((short)0); 195 SheetFunc.createCellString(workBook, row, (short)0, "燃料科长:",foterCellStyle); 196 HSSFCell cell1 = row.createCell((short)6); 197 SheetFunc.createCellString(workBook, row, (short)6, "审核:",foterCellStyle); 198 HSSFCell cell2 = row.createCell((short)11); 199 SheetFunc.createCellString(workBook, row, (short)11, "制表:",foterCellStyle); 200 } 201 for(short r=1;r<2;r++)// 202 { 203 HSSFRow row=sheet.createRow(headRow+perRow+r);// 204 HSSFCell cell = row.createCell((short)6); 205 SheetFunc.createCellString(workBook, row, (short)6, "第"+numStr+"页",foterCellStyle); 206 HSSFCell cell2 = row.createCell((short)7); 207 SheetFunc.createCellString(workBook, row, (short)7, "共"+totalSheetNum+"页",foterCellStyle); 208 } 209 } 210 211 //最后一页处理 212 for(int num=addSheetNum;num<=addSheetNum;num++) 213 { 214 HSSFSheet sheet = workBook.getSheetAt(num); 215 for(short r=0;r<lastSheetRow;r++) 216 { 217 HSSFRow row=sheet.createRow(headRow+r);// 218 int temp=num*perRow+r;//减少循环中的计算量 219 for(short c=0;c<2;c++) 220 { 221 HSSFCell cell = row.createCell(c);// 222 SheetFunc.createCellString(workBook, row, c, sqlDataString[temp][c],cellStyle); 223 } 224 for(short c=2;c<7;c++) 225 { 226 HSSFCell cell = row.createCell(c);// 227 if(sqlDataInt[num*perRow+r][c-2] ==0) 228 { 229 SheetFunc.createCellString(workBook, row, c, " ",cellStyle); 230 }else{ 231 SheetFunc.createCellInt(workBook, row, c,sqlDataInt[temp][c-2],cellStyle); 232 } 233 } 234 235 for(short c=7;c<12;c++) 236 { 237 HSSFCell cell = row.createCell(c);// 238 if(sqlDataFloat[num*perRow+r][c-7] == 0f) 239 { 240 SheetFunc.createCellString(workBook, row, c, " ",cellStyle); 241 }else{ 242 SheetFunc.createCellFloat(workBook, row, c, sqlDataFloat[temp][c-7], cellStyle); 243 } 244 } 245 for(short c=12;c<13;c++) 246 { 247 HSSFCell cell = row.createCell(c);// 248 SheetFunc.createCellString(workBook, row, c, sqlDataString[temp][c-10],cellStyle); 249 } 250 } 251 for(int j=0;j<lastSheetRow;j=j+2)//合并行 252 { 253 sheet.addMergedRegion(new Region(7+j,(short)0,8+j,(short)0)); 254 } 255 256 for(short r=0;r<1;r++)// 257 { 258 HSSFRow row=sheet.createRow(headRow+lastSheetRow+r);// 259 HSSFCell cell = row.createCell((short)0); 260 SheetFunc.createCellString(workBook, row, (short)0, "燃料科长:",foterCellStyle); 261 HSSFCell cell1 = row.createCell((short)6); 262 SheetFunc.createCellString(workBook, row, (short)6, "审核:",foterCellStyle); 263 HSSFCell cell2 = row.createCell((short)11); 264 SheetFunc.createCellString(workBook, row, (short)11, "制表:",foterCellStyle); 265 } 266 for(short r=1;r<2;r++)// 267 { 268 HSSFRow row=sheet.createRow(headRow+lastSheetRow+r);// 269 HSSFCell cell = row.createCell((short)6); 270 SheetFunc.createCellString(workBook, row, (short)6, "第"+totalSheetNum+"页",foterCellStyle); 271 HSSFCell cell2 = row.createCell((short)7); 272 SheetFunc.createCellString(workBook, row, (short)7, "共"+totalSheetNum+"页",foterCellStyle); 273 } 274 } 275 OutputStream os = new FileOutputStream(destFileName); 276 workBook.write(os); 277 is.close(); 278 os.flush(); 279 os.close(); 280 request.setCharacterEncoding("UTF-8"); 281 response.setCharacterEncoding("UTF-8"); 282 283 response.setContentType("application/download;charset=utf-8"); 284 String[] Name=destFileName.split("\\\\"); 285 String FileName=Name[Name.length-1]; 286 //response.setHeader("Content-disposition", "attachment;filename="+java.net.URLEncoder.encode(KB, "UTF-8")+".xls\""); 287 response.setHeader("Content-disposition", "attachment;filename=" + new String( FileName.getBytes("gb2312"), "ISO8859-1" )); 288 289 OutputStream os2 = response.getOutputStream(); 290 //XLSTransformer 291 File tempFile = new File(destFileName); 292 FileInputStream fis = new FileInputStream(tempFile); 293 byte[] b = new byte[1024]; 294 while(fis.read(b) != -1){ 295 os2.write(b); 296 } 297 os2.flush(); 298 fis.close(); 299 os2.close(); 300 tempFile.delete(); 301 } catch (SQLException e1) { 302 // TODO Auto-generated catch block 303 e1.printStackTrace(); 304 } 305 catch (Exception e) { 306 // TODO Auto-generated catch block 307 e.printStackTrace(); 308 } 309 } 310 }
SheetFunc.java
1 package tree; 2 3 import org.apache.poi.hssf.usermodel.HSSFCell; 4 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 5 import org.apache.poi.hssf.usermodel.HSSFRow; 6 import org.apache.poi.hssf.usermodel.HSSFSheet; 7 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 8 import org.apache.poi.hssf.util.Region; 9 10 public class SheetFunc { 11 public SheetFunc() { 12 13 } 14 private static String parseFormula(String pPOIFormula) { 15 final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$ 16 StringBuffer result = null; 17 int index; 18 19 result = new StringBuffer(); 20 index = pPOIFormula.indexOf(cstReplaceString); 21 if (index >= 0) { 22 result.append(pPOIFormula.substring(0, index)); 23 result.append(pPOIFormula.substring(index 24 + cstReplaceString.length())); 25 } else { 26 result.append(pPOIFormula); 27 } 28 29 return result.toString(); 30 } 31 32 public static void copyRows(HSSFWorkbook wb, int pSourceSheetIndex,int pTargetSheetIndex, int pStartRow, int pEndRow, int pPosition) 33 { 34 HSSFRow sourceRow = null; 35 HSSFRow targetRow = null; 36 HSSFCell sourceCell = null; 37 HSSFCell targetCell = null; 38 HSSFSheet sourceSheet = null; 39 HSSFSheet targetSheet = null; 40 Region region = null; 41 int cType; 42 int i; 43 short j; 44 int targetRowFrom; 45 int targetRowTo; 46 47 if ((pStartRow == -1) || (pEndRow == -1)) { 48 return; 49 } 50 sourceSheet = wb.getSheetAt(pSourceSheetIndex); 51 targetSheet = wb.getSheetAt(pTargetSheetIndex); 52 // 拷贝合并的单元格 53 for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) { 54 region = sourceSheet.getMergedRegionAt(i); 55 if ((region.getRowFrom() >= pStartRow) 56 && (region.getRowTo() <= pEndRow)) { 57 targetRowFrom = region.getRowFrom() - pStartRow + pPosition; 58 targetRowTo = region.getRowTo() - pStartRow + pPosition; 59 region.setRowFrom(targetRowFrom); 60 region.setRowTo(targetRowTo); 61 targetSheet.addMergedRegion(region); 62 } 63 } 64 // 设置列宽 65 for (i = pStartRow; i <= pEndRow; i++) { 66 sourceRow = sourceSheet.getRow(i); 67 if (sourceRow != null) { 68 for (j = sourceRow.getLastCellNum(); j > sourceRow 69 .getFirstCellNum(); j--) { 70 targetSheet 71 .setColumnWidth(j, sourceSheet.getColumnWidth(j)); 72 targetSheet.setColumnHidden(j, false); 73 } 74 break; 75 } 76 } 77 // 拷贝行并填充数据 78 for (; i <= pEndRow; i++) { 79 sourceRow = sourceSheet.getRow(i); 80 if (sourceRow == null) { 81 continue; 82 } 83 targetRow = targetSheet.createRow(i - pStartRow + pPosition); 84 targetRow.setHeight(sourceRow.getHeight()); 85 for (j = sourceRow.getFirstCellNum(); j < sourceRow 86 .getPhysicalNumberOfCells(); j++) { 87 sourceCell = sourceRow.getCell(j); 88 if (sourceCell == null) { 89 continue; 90 } 91 targetCell = targetRow.createCell(j); 92 targetCell.setEncoding(sourceCell.getEncoding()); 93 targetCell.setCellStyle(sourceCell.getCellStyle()); 94 cType = sourceCell.getCellType(); 95 targetCell.setCellType(cType); 96 switch (cType) { 97 case HSSFCell.CELL_TYPE_BOOLEAN: 98 targetCell.setCellValue(sourceCell.getBooleanCellValue()); 99 //System.out.println("--------TYPE_BOOLEAN:"+ targetCell.getBooleanCellValue()); 100 break; 101 case HSSFCell.CELL_TYPE_ERROR: 102 targetCell.setCellErrorValue(sourceCell.getErrorCellValue()); 103 //System.out.println("--------TYPE_ERROR:"+ targetCell.getErrorCellValue()); 104 break; 105 case HSSFCell.CELL_TYPE_FORMULA: 106 // parseFormula这个函数的用途在后面说明 107 targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula())); 108 //System.out.println("--------TYPE_FORMULA:"+ targetCell.getCellFormula()); 109 break; 110 case HSSFCell.CELL_TYPE_NUMERIC: 111 targetCell.setCellValue(sourceCell.getNumericCellValue()); 112 //System.out.println("--------TYPE_NUMERIC:"+ targetCell.getNumericCellValue()); 113 break; 114 case HSSFCell.CELL_TYPE_STRING: 115 targetCell.setCellValue(sourceCell.getRichStringCellValue()); 116 //System.out.println("--------TYPE_STRING:" + i+ targetCell.getRichStringCellValue()); 117 break; 118 } 119 } 120 } 121 } 122 public static void createCellString(HSSFWorkbook wb, HSSFRow row, short col, String val,HSSFCellStyle cellstyle) { 123 HSSFCell cell = row.createCell(col); 124 cell.setCellValue(val); 125 cell.setCellStyle(cellstyle); 126 } 127 public static void createCellInt(HSSFWorkbook wb, HSSFRow row, short col, int val,HSSFCellStyle cellstyle) { 128 HSSFCell cell = row.createCell(col); 129 // cell.setEncoding(HSSFCell.ENCODING_UTF_16); 130 cell.setCellValue(val); 131 cell.setCellStyle(cellstyle); 132 } 133 public static void createCellFloat(HSSFWorkbook wb, HSSFRow row, short col, float val,HSSFCellStyle cellstyle) { 134 HSSFCell cell = row.createCell(col); 135 String valStr = String.valueOf(val); 136 double db=Double.parseDouble(valStr); 137 cell.setCellValue(db); 138 cell.setCellStyle(cellstyle); 139 } 140 }
Flex 报表导出按钮函数:
1 //输出报表按钮事件 2 private function onExport():void{ 3 var sqlText:String="select KB,BYHLJ,JCMSL,CCSL,CCL,GHL,JCL,YD,YDZJE,KD,KDZJE,SPJE,BZ,GMDWDM from hc_jlykybb where to_char(YF,'yyyymm')='"+df.format(RQ.text)+"' order by GMDWDM,BYHLJ"; 4 var url:String="http://"+FlexGlobals.topLevelApplication.datas.IP+":80/lcds/servlet/JlykybToExcel"; // 下面的方法是调用后台生成excel 5 var request:URLRequest = new URLRequest(url); 6 request.method="POST"; 7 var variables:URLVariables = new URLVariables(); 8 variables.SQL =encodeURI(sqlText);//传入的SQL语句; 9 variables.PERROW =encodeURI("36");//每页记录数 10 var today:Date=new Date(); 11 var BSRQ:String=dfd.format(today); 12 var BBRQ:String=df.format(RQ.text); 13 variables.BSRQ=BSRQ;//20111227 14 variables.BBRQ=BBRQ;//200811需要传入的日期格式 15 16 variables.templateFileName=FlexGlobals.topLevelApplication.datas.xlsPath+"jcmjlykybb.xls"; 17 var dateString:String = df.format(today); 18 var DestF:String="d:\\计量盈亏"+dateString+".xls"; 19 variables.destFileName=encodeURI(DestF); 20 request.data = variables; 21 navigateToURL(request,"_blank"); 22 } 23 24 import flexlib.events.SuperTabEvent; 25 protected function onExit():void{ 26 dispatchEvent(new SuperTabEvent(SuperTabEvent.TAB_CLOSE,parentDocument.superTabNavigator.selectedIndex,true)); 27 }