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 }
1357
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
