jxl java工具类,导出excel,导入数据库
1: 引入jxl jar 我使用的为maven管理,
<!--Excel工具--> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </dependency>
以下做为工具类:
package my.utils; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.text.SimpleDateFormat; import java.util.*; /** * * 该工具类有4个重载的导出方法和1个导入方法,大家可以根据实际情况进行选择。 * 导入和导出方法都是通过传一个fieldMap参数(类的英文属性和Excel的中文列头的对应关系)来连接实体类和Excel的 * 导出的时候可以选择导出到本地文件系统或导出到浏览器,也可以自定义每个工作表的大小 * 导入的时候可以自定义业务主键组合uniqueFields,这样就可以检测Excel中是否有重复行了 */ public class ExcelUtil { /** * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * 如果需要的是引用对象的属性,则英文属性使用类似于EL表达式的格式 * 如:list中存放的都是student,student中又有college属性,而我们需要学院名称,则可以这样写 * fieldMap.put("college.collegeName","学院名称") * 一般的属性如 fieldMap.put("id","序号") 注:导入的时候fieldMap正好key,value相反写法 * @param sheetName 工作表的名称 * @param sheetSize 每个工作表中记录的最大个数 * @param out 导出流 * @throws ExcelException * @MethodName : listToExcel * @Description : 导出Excel(可以导出到本地文件系统,也可以导出到浏览器,可自定义工作表大小) */ public <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, OutputStream out) throws ExcelException { if (list.size() == 0 || list == null) { throw new ExcelException("数据源中没有任何数据"); } if (sheetSize > 65535 || sheetSize < 1) { sheetSize = 65535; } //创建工作簿并发送到OutputStream指定的地方 WritableWorkbook wwb; try { wwb = Workbook.createWorkbook(out); //因为2003的Excel一个工作表最多可以有65536条记录,除去列头剩下65535条 //所以如果记录太多,需要放到多个工作表中,其实就是个分页的过程 //1.计算一共有多少个工作表 double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue()); //2.创建相应的工作表,并向其中填充数据 for (int i = 0; i < sheetNum; i++) { //如果只有一个工作表的情况 if (1 == sheetNum) { WritableSheet sheet = wwb.createSheet(sheetName, i); fillSheet(sheet, list, fieldMap, 0, list.size() - 1); //有多个工作表的情况 } else { WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i); //获取开始索引和结束索引 int firstIndex = i * sheetSize; int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * sheetSize - 1; //填充工作表 fillSheet(sheet, list, fieldMap, firstIndex, lastIndex); } } wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); //如果是ExcelException,则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; //否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * @param out 导出流 * @throws ExcelException * @MethodName : listToExcel * @Description : 导出Excel(可以导出到本地文件系统,也可以导出到浏览器,工作表大小为2003支持的最大值) */ public <T> void listToExcel( List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, OutputStream out ) throws ExcelException { listToExcel(list, fieldMap, sheetName, 65535, out); } /** * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * @param sheetSize 每个工作表中记录的最大个数 * @param response 使用response可以导出到浏览器 * @throws ExcelException * @MethodName : listToExcel * @Description : 导出Excel(导出到浏览器,可以自定义工作表的大小) */ public <T> void listToExcel( List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, HttpServletResponse response ) throws ExcelException { //设置默认文件名为当前时间:年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString(); //设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); //改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); //创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); listToExcel(list, fieldMap, sheetName, sheetSize, out); } catch (Exception e) { e.printStackTrace(); //如果是ExcelException,则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; //否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * @param response 使用response可以导出到浏览器 * @throws ExcelException * @MethodName : listToExcel * @Description : 导出Excel(导出到浏览器,工作表的大小是2003支持的最大值) */ public <T> void listToExcel( List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, HttpServletResponse response ) throws ExcelException { listToExcel(list, fieldMap, sheetName, 65535, response); } /** * @param in :承载着Excel的输入流 * @param entityClass :List中对象的类型(Excel中的每一行都要转化为该类型的对象) * @param fieldMap :Excel中的中文列头和类的英文属性的对应关系Map * 如 map.put("序号","id") * @param uniqueFields :指定业务主键组合(即复合主键),这些列的组合不能重复 * (如:excel中列名 "序号","名称" = String[] uniqueFields=new String[]{"序号","名称"}) * @return :List * @throws ExcelException * @MethodName : excelToList * @Description : 将Excel转化为List */ public <T> List<T> excelToList( InputStream in, String sheetName, Class<T> entityClass, LinkedHashMap<String, String> fieldMap, String[] uniqueFields ) throws ExcelException { //定义要返回的list List<T> resultList = new ArrayList<T>(); try { //根据Excel数据源创建WorkBook Workbook wb = Workbook.getWorkbook(in); //获取工作表 Sheet sheet = wb.getSheet(sheetName); //获取工作表的有效行数 int realRows = 0; for (int i = 0; i < sheet.getRows(); i++) { int nullCols = 0; for (int j = 0; j < sheet.getColumns(); j++) { Cell currentCell = sheet.getCell(j, i); if (currentCell == null || "".equals(currentCell.getContents().toString())) { nullCols++; } } if (nullCols == sheet.getColumns()) { break; } else { realRows++; } } // System.out.println("=realRows一共行数="+realRows); //如果Excel中没有数据则提示错误 if (realRows <= 1) { throw new ExcelException("Excel文件中没有任何数据"); } //取第一行数据,(表头) Cell[] firstRow = sheet.getRow(0); //将第一行数据转为字符串数组 String[] excelFieldNames = new String[firstRow.length]; //获取Excel中的列名 for (int i = 0; i < firstRow.length; i++) { excelFieldNames[i] = firstRow[i].getContents().toString().trim(); } //判断需要的字段在Excel中是否都存在 boolean isExist = true; List<String> excelFieldList = Arrays.asList(excelFieldNames); for (String cnName : fieldMap.keySet()) { if (!excelFieldList.contains(cnName)) { isExist = false; break; } } //如果有列名不存在,则抛出异常,提示错误 if (!isExist) { throw new ExcelException("Excel中缺少必要的字段,或字段名称有误"); } //将列名和列号放入Map中,这样通过列名就可以拿到列号 (excelFieldNames,第一行数据-列名) LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>(); for (int i = 0; i < excelFieldNames.length; i++) { colMap.put(excelFieldNames[i], firstRow[i].getColumn()); } //判断是否有重复行 //1.获取uniqueFields指定的列 Cell[][] uniqueCells = new Cell[uniqueFields.length][]; for (int i = 0; i < uniqueFields.length; i++) { int col = colMap.get(uniqueFields[i]); uniqueCells[i] = sheet.getColumn(col); } //2.从指定列中寻找重复行 for (int i = 1; i < realRows; i++) { int nullCols = 0; for (int j = 0; j < uniqueFields.length; j++) { String currentContent = uniqueCells[j][i].getContents(); Cell sameCell = sheet.findCell(currentContent, uniqueCells[j][i].getColumn(), uniqueCells[j][i].getRow() + 1, uniqueCells[j][i].getColumn(), uniqueCells[j][realRows - 1].getRow(), true); if (sameCell != null) { nullCols++; } } if (nullCols == uniqueFields.length) { throw new ExcelException("Excel中有重复行,请检查"); } } //将sheet转换为list for (int i = 1; i < realRows; i++) { //新建要转换的对象 T entity = entityClass.newInstance(); //给对象中的字段赋值 for (Map.Entry<String, String> entry : fieldMap.entrySet()) { //获取中文字段名 String cnNormalName = entry.getKey(); //获取英文字段名 String enNormalName = entry.getValue(); //根据中文字段名获取列号 int col = colMap.get(cnNormalName); //获取当前单元格中的内容 String content = sheet.getCell(col, i).getContents().toString().trim(); //给对象赋值 setFieldValueByName(enNormalName, content, entity); } resultList.add(entity); } } catch (Exception e) { e.printStackTrace(); //如果是ExcelException,则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; //否则将其它异常包装成ExcelException再抛出 } else { e.printStackTrace(); throw new ExcelException("导入Excel失败"); } } return resultList; } /*<-------------------------辅助的私有方法----------------------------------------------->*/ /** * @param fieldName 字段名 * @param o 对象 * @return 字段值 * @MethodName : getFieldValueByName * @Description : 根据字段名获取字段值 */ private Object getFieldValueByName(String fieldName, Object o) throws Exception { Object value = null; Field field = getFieldByName(fieldName, o.getClass()); if (field != null) { field.setAccessible(true); value = field.get(o); } else { throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName); } return value; } /** * @param fieldName 字段名 * @param clazz 包含该字段的类 * @return 字段 * @MethodName : getFieldByName * @Description : 根据字段名获取字段 */ private Field getFieldByName(String fieldName, Class<?> clazz) { //拿到本类的所有字段 Field[] selfFields = clazz.getDeclaredFields(); //如果本类中存在该字段,则返回 for (Field field : selfFields) { if (field.getName().equals(fieldName)) { return field; } } //否则,查看父类中是否存在此字段,如果有则返回 Class<?> superClazz = clazz.getSuperclass(); if (superClazz != null && superClazz != Object.class) { return getFieldByName(fieldName, superClazz); } //如果本类和父类都没有,则返回空 return null; } /** * @param fieldNameSequence 带路径的属性名或简单属性名 * @param o 对象 * @return 属性值 * @throws Exception * @MethodName : getFieldValueByNameSequence * @Description : * 根据带路径或不带路径的属性名获取属性值 * 即接受简单属性名,如userName等,又接受带路径的属性名,如student.department.name等 */ private Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception { Object value = null; //将fieldNameSequence进行拆分 String[] attributes = fieldNameSequence.split("\\."); if (attributes.length == 1) { value = getFieldValueByName(fieldNameSequence, o); } else { //根据属性名获取属性对象 Object fieldObj = getFieldValueByName(attributes[0], o); String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1); value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj); } return value; } /** * @param fieldName 字段名 * @param fieldValue 字段值 * @param o 对象 * @MethodName : setFieldValueByName * @Description : 根据字段名给对象的字段赋值 */ private void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception { Field field = getFieldByName(fieldName, o.getClass()); if (field != null) { field.setAccessible(true); //获取字段类型 Class<?> fieldType = field.getType(); //根据字段类型给字段赋值 if (String.class == fieldType) { field.set(o, String.valueOf(fieldValue)); } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { field.set(o, Integer.parseInt(fieldValue.toString())); } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) { field.set(o, Long.valueOf(fieldValue.toString())); } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) { field.set(o, Float.valueOf(fieldValue.toString())); } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) { field.set(o, Short.valueOf(fieldValue.toString())); } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { field.set(o, Double.valueOf(fieldValue.toString())); } else if (Character.TYPE == fieldType) { if ((fieldValue != null) && (fieldValue.toString().length() > 0)) { field.set(o, Character .valueOf(fieldValue.toString().charAt(0))); } } else if (Date.class == fieldType) { field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString())); } else { field.set(o, fieldValue); } } else { throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName); } } /** * @param ws * @MethodName : setColumnAutoSize * @Description : 设置工作表自动列宽和首行加粗 */ private void setColumnAutoSize(WritableSheet ws, int extraWith) { //获取本列的最宽单元格的宽度 for (int i = 0; i < ws.getColumns(); i++) { int colWith = 0; for (int j = 0; j < ws.getRows(); j++) { String content = ws.getCell(i, j).getContents().toString(); int cellWith = content.length(); if (colWith < cellWith) { colWith = cellWith; } } //设置单元格的宽度为最宽宽度+额外宽度 ws.setColumnView(i, colWith + extraWith); } } /** * @param sheet 工作表 * @param list 数据源 * @param fieldMap 中英文字段对应关系的Map * @param firstIndex 开始索引 * @param lastIndex 结束索引 * @MethodName : fillSheet * @Description : 向工作表中填充数据 */ private <T> void fillSheet( WritableSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, int firstIndex, int lastIndex ) throws Exception { //定义存放英文字段名和中文字段名的数组 String[] enFields = new String[fieldMap.size()]; String[] cnFields = new String[fieldMap.size()]; //填充数组 int count = 0; for (Map.Entry<String, String> entry : fieldMap.entrySet()) { enFields[count] = entry.getKey(); cnFields[count] = entry.getValue(); count++; } //填充表头 for (int i = 0; i < cnFields.length; i++) { Label label = new Label(i, 0, cnFields[i]); sheet.addCell(label); } //填充内容 int rowNo = 1; for (int index = firstIndex; index <= lastIndex; index++) { //获取单个对象 T item = list.get(index); for (int i = 0; i < enFields.length; i++) { Object objValue = getFieldValueByNameSequence(enFields[i], item); String fieldValue = objValue == null ? "" : objValue.toString(); Label label = new Label(i, rowNo, fieldValue); sheet.addCell(label); } rowNo++; } //设置自动列宽 setColumnAutoSize(sheet, 5); } public static void main(String[] args) throws BiffException, IOException { // 1、构造excel文件输入流对象 String sFilePath = "D:/1.xls"; InputStream is = new FileInputStream(sFilePath); // 2、声明工作簿对象 Workbook rwb = Workbook.getWorkbook(is); // 3、获得工作簿的个数,对应于一个excel中的工作表个数 rwb.getNumberOfSheets(); Sheet oFirstSheet = rwb.getSheet(0);// 使用索引形式获取第一个工作表,也可以使用rwb.getSheet(sheetName);其中sheetName表示的是工作表的名称 // System.out.println("工作表名称:" + oFirstSheet.getName()); int rows = oFirstSheet.getRows();//获取工作表中的总行数 int columns = oFirstSheet.getColumns();//获取工作表中的总列数 for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { Cell oCell= oFirstSheet.getCell(j,i);//需要注意的是这里的getCell方法的参数,第一个是指定第几列,第二个参数才是指定第几行 System.out.println(oCell.getContents()+"\r\n"); } } } }
2:调用工具类方法:
导出:
List<Shebei> shebeis = shebeiService.findAllShebeis(getCurrentOrganization(context)); LinkedHashMap<String, String> fieldMap = new LinkedHashMap(); fieldMap.put("id", "序号"); fieldMap.put("name", "名称"); fieldMap.put("neicode", "内码"); fieldMap.put("code", "编码"); fieldMap.put("shebeiType.name", "设备种类"); fieldMap.put("shebeiPort", "设备端口数量"); fieldMap.put("address", "地址"); fieldMap.put("remark", "备注"); fieldMap.put("created", "创建日期"); fieldMap.put("modified", "修改日期"); fieldMap.put("member.nickname", "创建人"); File f = new File("d:" + File.separator + "ss.xls"); OutputStream out = null; out = new FileOutputStream(f); //设置监听 try { excelUtil.listToExcel(shebeis, fieldMap, "线缆", out); } catch (ExcelException ex) { } finally { try { if (out != null) { out.close(); } } catch (IOException e) { e.printStackTrace(); } }
导入:
这里我使用了一个dto类代替了entity类,
File f = new File("d:" + File.separator + "ss.xls"); InputStream input = null; input = new FileInputStream(f); LinkedHashMap<String, String> fieldMap = new LinkedHashMap(); fieldMap.put("序号", "id"); fieldMap.put("名称", "name"); fieldMap.put("内码", "neicode"); fieldMap.put("编码", "code"); fieldMap.put("设备种类", "shebeiType");//这个字段与其它实体类有关联,所以我使用了dto代替 fieldMap.put("设备端口数量", "shebeiPort"); fieldMap.put("地址", "address"); fieldMap.put("备注", "remark"); fieldMap.put("创建日期", "created"); fieldMap.put("修改日期", "modified"); fieldMap.put("创建人", "member"); String[] uniqueFields = new String[]{"序号","名称","内码","编码"}; List<ShebeiDto> shebeiDtos = excelUtil.excelToList(input,"线缆",ShebeiDto.class,fieldMap,uniqueFields);
//ShebeiDto
private Long id;
private String shebeiType;
private String member;
private Date created;
private Date modified;
..........
好了ok了
,关键在于组装
导入,导出LinkedHashMap<String, String> fieldMap = new LinkedHashMap();
导入:String[] uniqueFields = new String[]{"序号","名称","内码","编码"}; 这里是excel中的列名称,行不能重复,也就是数据库对应的字段不能重复{"id","name","code","codenei"}
注:
用java将数据生成excel 表格,主要利用jxl.jar 和poi.jar来实现。
我看过两段代码,刚好一个是jxl写的,另一个是poi写的,区别可以看出来,^_^ 下面这些看不出来的是前人总结的:
POI为apache公司的一个子项目,主要是提供一组操作windows文档的Java API.
JavaExcel俗称jxl是一开放源码项目,通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。使用该API非Windows操作系统也可以通过纯Java应用来处理Excel数据表。因为是使用Java编写的,所以我们在Web应用中可以通过JSP、Servlet来调用API实现对Excel数据表的访问。
就这两者的区别,主要谈下JVM虚拟机内存消耗的情况.
数据量3000条数据,每条60列.JVM虚拟机内存大小64M.
使用POI:运行到2800条左右就报内存溢出.
使用JXL:3000条全部出来,并且内存还有21M的空间.
可想而知,在对内存的消耗方面差距还是挺大的.
也许是由于JXL在对资源回收利用方面做的还挺不错的.
关于两者效率方面,也是基于大数据量而言的,数据量小的话基本上差别不大,也不难被发觉.但是大的数据量,POI消耗的JVM内存远比JXL消耗的多.但相比提供的功能的话,JXL又相对弱了点.所以如果要实现的功能比较复杂的情况下可以考虑使用POI,但如果只想生成一些大数据量可以考虑使用JXL,或者CSV也是一个不错的选择,不过CSV并不是真正的excel