Java Excel操作工具类,创建一个Excel,只需一行代码。
package com.hanyastar.util.file; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import com.hanyastar.exception.HanyaException; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.read.biff.BiffException; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; /** * Excel操作. * 支持读、写操作。 * @author Belen * @since 2013-05-26 */ public abstract class XLSUtils { /** * 生成Excel. <br/> * Excel中columnsName将加粗显示。 * Example: * * <pre> * 列名数组 * String [] columnsName = new String[]{"id","username","password"} * * 内容List,此List中存放着数组,要求内容为字符串 * List<String[]> sList = new ArrayList(); * sList.add(new String[]{"1","u1","p1"}); * sList.add(new String[]{"2","u2","p2"}); * sList.add(new String[]{"3","u3","p3"}); * * CreateXLS("e:/", "1.xls",columnsName , sList); * 执行后,将指定路径生成Excel. * </pre> * * @param path * 生成文件的路径 绝对路径 * @param fileName * 文件名 * @param column * EXCEL第一行的列名 * @param contentList * EXCEL每行的数据,List中存放字符串型 * @throws HanyaException */ public static void CreateXLS(String path, String fileName, String[] columnsName, List<String[]> contentList) throws HanyaException{ WritableWorkbook book = null; try { // 设置表头格式 WritableCellFormat wcf = new WritableCellFormat( new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK)); // 设置对齐方式 wcf.setAlignment(jxl.format.Alignment.CENTRE); // 垂直居中 // wCF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); book = Workbook.createWorkbook(new File(path + fileName)); // 生成名为“第一页”的工作表,参数0表示这是第一页 WritableSheet sheet = book.createSheet(fileName, 0); // 添加第一行的列名 for (int i = 0; i < columnsName.length; i++) { // Label 列 行 内容 sheet.addCell(new Label(i, 0, columnsName[i], wcf)); if (columnsName[i].length() > 5) { sheet.setColumnView(i, 20); } } // 添加每列每行的内容 for (int j = 0; j < contentList.size(); j++) { String[] sArray = (String[]) contentList.get(j); for (int i = 0; i < sArray.length; i++) { sheet.addCell(new Label(i, j + 1, sArray[i])); } } book.write(); } catch (IOException e) { throw new HanyaException(e); } catch (RowsExceededException e1) { throw new HanyaException(e1); } catch (WriteException e2) { throw new HanyaException(e2); } finally { if (book != null){ try { book.close(); } catch (WriteException e) { throw new HanyaException(e); } catch (IOException e) { throw new HanyaException(e); } } } } /** * 根据filename,column,list生在Excel. * @throws HanyaException * * @see CreateXLS(String path, String fileName, String column[], List<String[]> * contentList) */ public static void CreateXLS(String fileName, String columnsName[], List<String[]> contentList) throws HanyaException { CreateXLS("", fileName, columnsName, contentList); } /** * 根据filename,list生在Excel. CreateXLS(String fileName,String column[], List<String[]> * contentList) * @throws HanyaException */ public static void CreateXLS(String fileName, List<String[]> contentList) throws HanyaException { CreateXLS("", fileName, new String[] {}, contentList); } /** * 将Excel转化为List,此List中的值均为数组。Excel中一行数据为一个数组。<br/> * Example: * <pre> * List<String[]> cList = readXLS("e:/1.xls"); * for (int i = 0; i < cList.size(); i++) { * String[] s = cList.get(i); * int t = 0; * while (t < s.length) { * System.out.println(s[t]); * t++; * } * } * ---------------------------- * 输出:每行数据 * </pre> * @param path EXCEL的路径,必须要为绝对路径。 * @return list * @throws HanyaException */ public static List<String[]> readXLS(String path) throws HanyaException { Workbook book = null; try { List<String[]> cList = new ArrayList<String[]>(); book = Workbook.getWorkbook(new File(path)); Sheet[] sheets = book.getSheets(); for (int i = 0; i < sheets.length; i++) { Sheet sheet = sheets[i]; int rows = sheet.getRows(); int columns = sheet.getColumns(); for (int j = 0; j < rows; j++) { String[] contentArray = new String[columns]; int t = 0; while (t < columns) { Cell cell = sheet.getCell(t, j); contentArray[t] = cell.getContents(); t++; } cList.add(contentArray); } } return cList; } catch (BiffException e) { throw new HanyaException(e); } catch (IOException e1) { throw new HanyaException(e1); } finally { if (book != null) book.close(); } } }
创建与读取EXCEL只需一行或很少量代码。
将EXCEL自动转成JAVA对象等均可以。
附件包括源码与架包,下载后放在项目中就可运行,适当去掉我调用的异常处理即可。
补充:软件开发 , Java ,