excel导入导出通用方法

1.package mon.utils;2.import java.io.OutputStream;3.import java.util.List;4.import javax.servlet.http.HttpServletResponse;5.import org.apache.struts2.ServletActionContext;6.import ng.reflect.Field;7.8.import jxl.Workbook;9.import jxl.format.Alignment;10.import jxl.format.Border;11.import jxl.format.BorderLineStyle;12.import jxl.format.VerticalAlignment;13.import bel;14.import jxl.write.WritableCellFormat;15.import jxl.write.WritableFont;16.import jxl.write.WritableSheet;17.import jxl.write.WritableWorkbook;18./***19. * @author lsf20. */21.public class ExportExcel {22./***************************************************************************23. * @param fileName EXCEL文件名称24. * @param listTitle EXCEL文件第一行列标题集合25. * @param listContent EXCEL文件正文数据集合26. * @return27. */28.public final static String exportExcel(String fileName,String[] Title, List<Object> listContent) {29. String result="系统提示：Excel文件导出成功！";30.// 以下开始输出到EXCEL31.try {32.//定义输出流，以便打开保存对话框______________________begin33. HttpServletResponse response=ServletActionContext.getResponse();34. OutputStream os = response.getOutputStream();// 取得输出流35. response.reset();// 清空输出流36. response.setHeader("Content-disposition", "attachment; filename="+ new String(fileName.getBytes("GB2312"),"ISO8859-1"));37.// 设定输出文件头38. response.setContentType("application/msexcel");// 定义输出类型39.//定义输出流，以便打开保存对话框_______________________end40.41./** **********创建工作簿************ */42. WritableWorkbook workbook = Workbook.createWorkbook(os);43.44./** **********创建工作表************ */45.46. WritableSheet sheet = workbook.createSheet("Sheet1", 0);47.48./** **********设置纵横打印（默认为纵打）、打印纸***************** */49. jxl.SheetSettings sheetset = sheet.getSettings();50. sheetset.setProtected(false);51.52.53./** ************设置单元格字体************** */54. WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);55. WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);56.57./** ************以下设置三种单元格样式，灵活备用************ */58.// 用于标题居中59. WritableCellFormat wcf_center = new WritableCellFormat(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. WritableCellFormat wcf_left = new WritableCellFormat(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(new Label(0, 0, "XX报表", wcf_center));76./** ***************以下是EXCEL第一行列标题********************* */77.for (int i = 0; i < Title.length; i++) {78. sheet.addCell(new Label(i, 0,Title[i],wcf_center));79. }80./** ***************以下是EXCEL正文数据********************* */81. Field[] fields=null;82.int i=1;83.for(Object obj:listContent){84. fields=obj.getClass().getDeclaredFields();85.int j=0;86.for(Field v:fields){87. v.setAccessible(true);88. Object va=v.get(obj);89.if(va==null){90. va="";91. }92. sheet.addCell(new Label(j, i,va.toString(),wcf_left));93. j++;94. }95. i++;96. }97./** **********将以上缓存中的内容写到EXCEL文件中******** */98. workbook.write();99./** *********关闭文件************* */100. workbook.close();101.102. } catch (Exception e) {103. result="系统提示：Excel文件导出失败，原因："+ e.toString();104. System.out.println(result);105. e.printStackTrace();106. }107.return result;108.}109.}3.通用导出：[java]view plaincopy1.package mon.excel.parser;2.3.4.import java.io.FileOutputStream;5.import java.io.OutputStream;6.import ng.reflect.Field;7.import ng.reflect.Method;8.import java.util.ArrayList;9.import java.util.Collection;10.import java.util.Date;11.import java.util.HashMap;12.import java.util.Iterator;13.import java.util.List;14.import java.util.Map;15.16.17.import ermodel.HSSFRichTextString;18.import ermodel.HSSFWorkbook;19.import ermodel.Cell;20.import ermodel.RichTextString;21.import ermodel.Row;22.import ermodel.Sheet;23.import ermodel.Workbook;24.25.26.import com.huateng.test.pojo.Student;27.28.29.public class ExcelExport2 {30.31.32.public static void exportExcel(String title, Class pojoClass,Collection dataSet,33.34.//使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出35.36.//使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现37.38.private static void exportExcelInUserModel(String title, Class pojoClass,Collection dataSet,39.40.try {41.// 首先检查数据看是否是正确的42.if (dataSet == null || dataSet.size()==0) {43.throw new Exception("导出数据为空！");44.45.if(title == null || out == null || pojoClass == null)46.47.throw new Exception("传入参数不能为空！");48.49.// 声明一个工作薄50.new HSSFWorkbook();51.// 生成一个表格52.53.54.55.// 标题56.new ArrayList<String>();57.new ArrayList<Integer>();58.// 拿到所有列名，以及导出的字段的get方法59.new ArrayList<Method>();60.new HashMap<String,Method>();61.// 得到所有字段62.63.// 遍历整个filed64.for (int i = 0; i < fileds.length; i++) {65.66.class);67.// 如果设置了annottion68.if (excel != null) {69.// 添加到标题70.71.//添加标题的列宽72.73.// 添加到需要导出的字段的方法74.75.//System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth());76.new StringBuffer("get");77.0, 1)78.79.1));80.81.82.83.new Class[] {});84.85.86.87.if(excel.exportConvertSign()==1)88.89.new StringBuffer("get");90.0, 1)91.92.1));93."Convert");94.//System.out.println("convert: "+getConvertMethodName.toString());95.96.new Class[] {});97.98.99.100.101.i nt index = 0;102.// 产生表格标题行103.104.f or (int i = 0,exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { 105.106.// cell.setCellStyle(style);107.new HSSFRichTextString(108.109.110.111.112.113.//设置每行的列宽114.f or (int i = 0; i < exportFieldWidth.size(); i++) {115.//256=65280/255116.256*exportFieldWidth.get(i));117.118.119.// 循环插入剩下的集合120.w hile (its.hasNext()) {121.// 从第二行开始写，第一行是标题122.123.124.125.f or (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { 126.127.128.null;129.i f(convertMethod.containsKey(getMethod.getName()))130.131.132.new Object[] {});133.else134.135.new Object[] {});136.137.138.139.140.141.142.143.catch (Exception e) {144.145.147.148.149.150.151.p ublic static void main(String[] args) throws Exception {152.153.154.// 构造一个模拟的List来测试，实际使用时，这个集合用从数据库中查出来155.new Student();156."第一行数据");157.28);158.2);159."abcdefghijklmnop");160.new Date());161.true);162.new ArrayList();163.164.f or (int i = 0; i < 50000; i++) {165.new Student();166."一二三四五六七八九");167.22);168.1);169."abcdefghijklmnop");170.new Date());171.false);172.173.174.// 构造输出对象，可以从response输出，直接向用户提供下载175.new FileOutputStream("D://testOne.xls"); 176.// 开始时间177.178.// 注意179.new ExcelExport2();180.//181."测试",Student.class,list, out);182.183.// 结束时间184.185."excel导出成功");186."总共耗时：" + (s - l));187.188.189.191.192.4.通用导入:[java]view plaincopy1.package mon.excel.parser;2.3.4.import java.io.File;5.import java.io.FileInputStream;6.import ng.reflect.Field;7.import ng.reflect.Method;8.import ng.reflect.Type;9.import java.text.SimpleDateFormat;10.import java.util.ArrayList;11.import java.util.Collection;12.import java.util.HashMap;13.import java.util.Iterator;14.import java.util.List;15.import java.util.Map;16.17.18.import ermodel.HSSFSheet;19.import ermodel.HSSFWorkbook;20.import ermodel.Cell;21.import ermodel.Row;22.23.24.import com.huateng.test.pojo.Student;25.26.27.public class ExcelImport2 {28.29.public Collection importExcel(File file ,Class pojoClass,String... pattern) {30.31.new ArrayList();32.try {33.// 得到目标目标类的所有的字段列表34.35.// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中36.new HashMap<String,Method>();37.38.new HashMap<String,Method>();39.40.41.// 循环读取所有字段42.for (int i = 0; i < filed.length; i++) {43.44.45.// 得到单个字段上的Annotation46.class);47.48.// 如果标识了Annotationd的话49.if (excel != null) {50.// 构造设置了Annotation的字段的Setter方法51.52."set"53.0, 1).toUpperCase()54.1);55.// 构造调用的method，56.57.new Class[] { f.getType() });58.// 将这个method以Annotaion的名字为key来存入。