利用jxl读写excel

项目中经常用用到excel的导入和导出，这里分享一个自己常用的工具类。代码如下。

import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.math.BigDecimal;import java.util.ArrayList;import java.util.List;import jxl.Sheet;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.VerticalAlignment;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;/** *  * 读写Excel文件的工具类 *  * @since [产品/模块版本] */public class ExcelTools {/** * 读Excel文件的方法。 <功能详细描述> *  * @param fileName *            读取文件名（包含路径) * @param sheetNum */public static List<String[]> readExcel(String fileName, int sheetNum) {File file = new File(fileName);InputStream is = null;Workbook rwb = null;Sheet stFile = null;List<String[]> list = new ArrayList<String[]>();if (file.exists() && file.length() > 0) {try {is = new FileInputStream(file);rwb = Workbook.getWorkbook(is);stFile = rwb.getSheet(sheetNum);int cols = stFile.getColumns();for (int r = 0; r < stFile.getRows(); r++) {String[] record = new String[cols];for (int c = 0; c < cols; c++) {record[c] = stFile.getCell(c, r).getContents().trim();}list.add(record);}} catch (BiffException ex) {ex.printStackTrace();} catch (IOException ex) {ex.printStackTrace();} finally {rwb.close();if (is != null) {try {is.close();} catch (IOException e) {e.printStackTrace();}}}}return list;}/** * 读Excel文件的方法。 <功能详细描述> *  * @param fileName *            读取文件名（包含路径) * @param sheetNum * @return [参数说明] *  * @return List<String[]> [返回类型说明] * @exception throws [违例类型] [违例说明] * @see [类、类#方法、类#成员] */public static List<String[]> readExcel(File file, int sheetNum) {InputStream is = null;Workbook rwb = null;Sheet stFile = null;List<String[]> list = new ArrayList<String[]>();if (file.exists() && file.length() > 0) {try {is = new FileInputStream(file);rwb = Workbook.getWorkbook(is);stFile = rwb.getSheet(sheetNum);int cols = stFile.getColumns();for (int r = 0; r < stFile.getRows(); r++) {String[] record = new String[cols];for (int c = 0; c < cols; c++) {record[c] = stFile.getCell(c, r).getContents().trim();}list.add(record);}} catch (BiffException ex) {ex.printStackTrace();} catch (IOException ex) {ex.printStackTrace();} finally {rwb.close();if (is != null) {try {is.close();} catch (IOException e) {e.printStackTrace();}}}}return list;}@SuppressWarnings("unchecked")public static String writeExcel(List listtitle, List listtype, List listcontent,List<Integer> widths,List<Boolean> iswrap, String filedirpath, String filesavename, String title, int titlesize) {File filepath = new File(filedirpath);if (filepath.exists()) {// log.info("路径已经存在！<br/>");} else {filepath.mkdir();}String sCurrPath = filedirpath + "/" + filesavename ;// String myfilepath = request.getRealPath(sCurrPath);File file = new File(sCurrPath);try {file.createNewFile();// 获取文件的URL地址} catch (Exception e) {// log.error("创建文件失败！");}// 以下开始输出到EXCELtry {/************ 创建工作簿 *************/WritableWorkbook workbook = Workbook.createWorkbook(new File(sCurrPath));/************ 创建工作表 *************/WritableSheet sheet = workbook.createSheet("导出项目", 0);// sheet.setPageSetup(5,true);/************ 设置纵横打印（默认为纵打）、打印纸 ******************/// sheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,0,0);// sheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,PaperSize.A4,0,0);// sheet.addRowPageBreak(12);jxl.SheetSettings sheetset = sheet.getSettings();sheetset.setProtected(false);// sheet.setColumnView(0,5);// sheet.setColumnView(1,12);/************** 设置单元格字体 ***************/WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);WritableFont Font = new WritableFont(WritableFont.ARIAL, 10,WritableFont.NO_BOLD);/************** 以下设置几种格式的单元格 *************/// 用于正文居左WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条wcf_left.setVerticalAlignment(VerticalAlignment.TOP); // 文字垂直对齐wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐wcf_left.setWrap(false); // 文字是否换行// 用于正文居右WritableCellFormat wcf_right = new WritableCellFormat(NormalFont);wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐wcf_right.setAlignment(Alignment.RIGHT); // 文字水平对齐wcf_right.setWrap(false); // 文字是否换行// 用于正文居中WritableCellFormat wcf_center = new WritableCellFormat(Font);//wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐wcf_center.setWrap(false); // 文字是否换行// 用于正文居中标题WritableCellFormat wcf_title = new WritableCellFormat(NormalFont);wcf_title.setBorder(Border.ALL, BorderLineStyle.THICK); // 线条wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐wcf_title.setAlignment(Alignment.CENTRE); // 文字水平对齐wcf_title.setWrap(false); // 文字是否换行// 用于跨行WritableCellFormat wcf_merge = new WritableCellFormat(BoldFont);wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条wcf_merge.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐wcf_merge.setAlignment(Alignment.CENTRE); // 文字水平对齐wcf_merge.setWrap(true); // 文字是否换行//换行加居左WritableCellFormat wcf_merge_left = new WritableCellFormat(Font);//wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条wcf_merge.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐wcf_merge.setAlignment(Alignment.LEFT); // 文字水平对齐wcf_merge.setWrap(true); // 文字是否换行/************** 单元格格式设置完成 *******************/for(int i=0 ;i<widths.size();i++){sheet.setColumnView(i, widths.get(i));}/***************** 以下是定单内容 **********************/sheet.mergeCells(0, 0, titlesize, 0);sheet.addCell(new Label(0, 0, title, wcf_center));for (int i = 0; i < listtitle.size(); i++) {sheet.addCell(new Label(i, 1, (String) listtitle.get(i),wcf_title));}for (int i = 0; i < listcontent.size(); i++) {// 写入数据List contentlist = (List) listcontent.get(i);for (int j = 0; j < contentlist.size(); j++) {String datatype = (String) listtype.get(j);if ("string".equals(datatype)){if(iswrap.get(j)){sheet.addCell(new Label(j, i + 2, (String) contentlist.get(j), wcf_merge_left)); //换行}else{sheet.addCell(new Label(j, i + 2, (String) contentlist.get(j), wcf_center)); // 不换行}}else if ("float".equals(datatype)) {jxl.write.Number labelN = new jxl.write.Number(j,i + 2, new BigDecimal((String) contentlist.get(j)).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());sheet.addCell(labelN);}}}/************ 以上所写的内容都是写在缓存中的，下一句将缓存的内容写到文件中 *********/workbook.write();/*********** 关闭文件 **************/workbook.close();} catch (Exception e) {e.printStackTrace();// log.error("在输出到EXCEL的过程中出现错误，错误原因：" + e.toString());}return filesavename;}}

代码注释已经很清楚了，就不再介绍啦。