sheetToExcel.java
View Code 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 SheetFunc.java
View Code 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 报表导出按钮函数：
View Code 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 } 模版文件目录：D:\apache-tomcat-6.0.32\webapps\lcds\WEB-INF\classes\tree\xls
导出效果图：
