')");
}
catch(OleDbExceptionerr)
{
Response.Write("导入数据库时出错:
"+err.ToString());
break;
}
}
conn.Close();
篇二:
excel导入导出通用方法
1.packagemon.utils;
2.importjava.io.OutputStream;
3.importjava.util.List;
4.importjavax.servlet.http.HttpServletResponse;
5.importorg.apache.struts2.ServletActionContext;
6.importjava.lang.reflect.Field;
7.
8.importjxl.Workbook;
9.importjxl.format.Alignment;
10.importjxl.format.Border;
11.importjxl.format.BorderLineStyle;
12.importjxl.format.VerticalAlignment;
13.importjxl.write.Label;
14.importjxl.write.WritableCellFormat;
15.importjxl.write.WritableFont;
16.importjxl.write.WritableSheet;
17.importjxl.write.WritableWorkbook;
18./***
19.*@authorlsf
20.*/
21.publicclassExportExcel{
22./**************************************************************************
*
23.*@paramfileNameEXCEL文件名称
24.*@paramlistTitleEXCEL文件第一行列标题集合
25.*@paramlistContentEXCEL文件正文数据集合
26.*@return
27.*/
28.publicfinalstaticStringexportExcel(StringfileName,String[]Title,Lis
t
29.Stringresult="系统提示:
Excel文件导出成功!
";
30.//以下开始输出到EXCEL
31.try{
32.//定义输出流,以便打开保存对话框______________________begin
33.HttpServletResponseresponse=ServletActionContext.getResponse();
34.OutputStreamos=response.getOutputStream();//取得输出流
35.response.reset();//清空输出流
36.response.setHeader("Content-disposition","attachment;filename="+newSt
ring(fileName.getBytes("GB2312"),"ISO8859-1"));
37.//设定输出文件头
38.response.setContentType("application/msexcel");//定义输出类型
39.//定义输出流,以便打开保存对话框_______________________end
40.
41./************创建工作簿*************/
42.WritableWorkbookworkbook=Workbook.createWorkbook(os);
43.
44./************创建工作表*************/
45.
46.WritableSheetsheet=workbook.createSheet("Sheet1",0);
47.
48./************设置纵横打印(默认为纵打)、打印纸******************/
49.jxl.SheetSettingssheetset=sheet.getSettings();
50.sheetset.setProtected(false);
51.
52.
53./**************设置单元格字体***************/
54.WritableFontNormalFont=newWritableFont(WritableFont.ARIAL,10);
55.WritableFontBoldFont=newWritableFont(WritableFont.ARIAL,10,WritableF
ont.BOLD);
56.
57./**************以下设置三种单元格样式,灵活备用*************/
58.//用于标题居中
59.WritableCellFormatwcf_center=newWritableCellFormat(BoldFont);
60.wcf_center.setBorder(Border.ALL,BorderLineStyle.THIN);//线条
61.wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE);//文字垂直对
齐
62.wcf_center.setAlignment(Alignment.CENTRE);//文字水平对齐
63.wcf_center.setWrap(false);//文字是否换行
64.
65.//用于正文居左
66.WritableCellFormatwcf_left=newWritableCellFormat(NormalFont);
67.wcf_left.setBorder(Border.NONE,BorderLineStyle.THIN);//线条
68.wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE);//文字垂直对齐
69.wcf_left.setAlignment(Alignment.LEFT);//文字水平对齐
70.wcf_left.setWrap(false);//文字是否换行
71.
72.
73./*****************以下是EXCEL开头大标题,暂时省略**********************/
74.//sheet.mergeCells(0,0,colWidth,0);
75.//sheet.addCell(newLabel(0,0,"XX报表",wcf_center));
76./*****************以下是EXCEL第一行列标题**********************/
77.for(inti=0;i
78.sheet.addCell(newLabel(i,0,Title[i],wcf_center));
79.}
80./*****************以下是EXCEL正文数据**********************/
81.Field[]fields=null;
82.inti=1;
83.for(Objectobj:
listContent){
84.fields=obj.getClass().getDeclaredFields();
85.intj=0;
86.for(Fieldv:
fields){
87.v.setAccessible(true);
88.Objectva=v.get(obj);
89.if(va==null){
90.va="";
91.}
92.sheet.addCell(newLabel(j,i,va.toString(),wcf_left));
93.j++;
94.}
95.i++;
96.}
97./************将以上缓存中的内容写到EXCEL文件中*********/
98.workbook.write();
99./***********关闭文件**************/
100.workbook.close();
101.
102.}catch(Exceptione){
103.result="系统提示:
Excel文件导出失败,原因:
"+e.toString();
104.System.out.println(result);
105.e.printStackTrace();
106.}
107.returnresult;
108.}
109.}
3.通用导出:
[java]viewplaincopy
1.packagemon.excel.parser;
2.
3.
4.importjava.io.FileOutputStream;
5.importjava.io.OutputStream;
6.importjava.lang.reflect.Field;
7.importjava.lang.reflect.Method;
8.importjava.util.ArrayList;
9.importjava.util.Collection;
10.importjava.util.Date;
11.importjava.util.HashMap;
12.importjava.util.Iterator;
13.importjava.util.List;
14.importjava.util.Map;
15.
16.
17.importorg.apache.poi.hssf.usermodel.HSSFRichTextString;
18.importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
19.importorg.apache.poi.ss.usermodel.Cell;
20.importorg.apache.poi.ss.usermodel.RichTextString;
21.importorg.apache.poi.ss.usermodel.Row;
22.importorg.apache.poi.ss.usermodel.Sheet;
23.importorg.apache.poi.ss.usermodel.Workbook;
24.
25.
26.importcom.huateng.test.pojo.Student;
27.
28.
29.publicclassExcelExport2{
30.
31.
32.publicstaticvoidexportExcel(Stringtitle,ClasspojoClass,Collectiondata
Set,
33.
34.//使用userModel模式实现的,当excel文档出现10万级别的大数据文件可能导致OOM内存溢
出
35.
36.//使用eventModel实现,可以一边读一边处理,效率较高,但是实现复杂,暂时未实现
37.
38.privatestaticvoidexportExcelInUserModel(Stringtitle,ClasspojoClass,Col
lectiondataSet,
39.
40.try{
41.//首先检查数据看是否是正确的
42.if(dataSet==null||dataSet.size()==0){
43.thrownewException("导出数据为空!
");
44.
45.if(title==null||out==null||pojoClass==null)
4