操作Excel工具类（基于jxl.jar）
关于JXL就不多做介绍了，这是鄙人自己封装的工具类
?
功能简介：
1、向Excel文档插入数据，可以是多行可以是多列，保留原单元格格式不变
2、向Excel文档插入一个新行，并且使用与上一行完全相同的格式
3、更改sheet的名字
4、拷贝一个sheet，与原sheet内容完全一致
?
需要的第三方JAR包：jxl.jar，见附件
?
package cn.chenfeng.excel;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.nio.channels.FileChannel;import jxl.Workbook;import jxl.format.CellFormat;import jxl.read.biff.BiffException;import jxl.write.Label;import jxl.write.WritableCell;import jxl.write.WritableCellFormat;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;/** * Excel工具类 * * @author 陈峰 */public class ExcelMakerUtil {private File excelFile;private Workbook workBook;private WritableWorkbook wWorkBook;public ExcelMakerUtil(File file) throws BiffException, IOException {this.excelFile = file;this.workBook = Workbook.getWorkbook(excelFile);this.wWorkBook = Workbook.createWorkbook(excelFile, this.workBook);}public void writeArrayToExcel(int sheetNum, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents)throws BiffException, IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);writeArrayToExcel(sheet, fillRow, startRowNum, startColumnNum, contents);}public void writeArrayToExcel(String sheetName, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents)throws BiffException, IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetName);writeArrayToExcel(sheet, fillRow, startRowNum, startColumnNum, contents);}private void writeArrayToExcel(WritableSheet sheet, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents)throws WriteException, RowsExceededException {for (int i = 0, length = contents.length; i < length; i++) {int rowNum;int columnNum;if (fillRow) {rowNum = startRowNum;columnNum = startColumnNum + i;} else {rowNum = startRowNum + i;columnNum = startColumnNum;}this.writeToCell(sheet, rowNum, columnNum,convertString(contents[i]));}}public void writeArrayToExcelReplaceHtml(int sheetNum, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents,boolean replaceHtml) throws BiffException, IOException,WriteException {if (replaceHtml) {for (int i = 0, length = contents.length; i < length; i++) {String value = convertString(contents[i]);contents[i] = replaceHtmlStr(value);}}this.writeArrayToExcel(sheetNum, fillRow, startRowNum, startColumnNum,contents);}public void writeArrayToExcelReplaceHtml(int sheetNum, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents,boolean lineWrap, boolean replaceHtml) throws BiffException,IOException, WriteException {if (replaceHtml) {for (int i = 0, length = contents.length; i < length; i++) {String value = convertString(contents[i]);contents[i] = replaceHtmlStr(value);}}this.writeArrayToExcel(sheetNum, fillRow, startRowNum, startColumnNum,contents, lineWrap);}public void writeArrayToExcel(int sheetNum, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents,boolean lineWrap) throws BiffException, IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);writeArrayToExcel(sheet, fillRow, startRowNum, startColumnNum,contents, lineWrap);}private void writeArrayToExcel(WritableSheet sheet, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents,boolean lineWrap) throws WriteException, RowsExceededException {for (int i = 0, length = contents.length; i < length; i++) {int rowNum;int columnNum;if (fillRow) {rowNum = startRowNum;columnNum = startColumnNum + i;} else {rowNum = startRowNum + i;columnNum = startColumnNum;}this.writeToCell(sheet, rowNum, columnNum,convertString(contents[i]), lineWrap);}}public void writeArrayToExcel(int sheetNum, boolean fillRow,String startColumnRowNum, Object[] contents) throws BiffException,IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);WritableCell startCell = sheet.getWritableCell(startColumnRowNum);int startRowNum = startCell.getRow();int startColumnNum = startCell.getColumn();this.writeArrayToExcel(sheetNum, fillRow, startRowNum, startColumnNum,contents);}public void writeToExcel(int sheetNum, int rowNum, int columnNum,Object value) throws BiffException, IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);this.writeToCell(sheet, rowNum, columnNum, value);}public void writeToExcel(int sheetNum, String columnRowNum, Object value)throws BiffException, IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);this.writeToCell(sheet, columnRowNum, value);}public void writeToExcel(String sheetName, String columnRowNum, Object value)throws BiffException, IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetName);this.writeToCell(sheet, columnRowNum, value);}private void writeToCell(WritableSheet sheet, int rowNum, int columnNum,Object value) throws WriteException, RowsExceededException {WritableCell cell = sheet.getWritableCell(columnNum, rowNum);writeToCell(sheet, cell, value);}private void writeToCell(WritableSheet sheet, String columnRowNum,Object value) throws WriteException, RowsExceededException {WritableCell cell = sheet.getWritableCell(columnRowNum);writeToCell(sheet, cell, value);}private void writeToCell(WritableSheet sheet, WritableCell cell,Object value) throws WriteException, RowsExceededException {CellFormat cellFormat = cell.getCellFormat();Label label;if (cellFormat == null) {label = new Label(cell.getColumn(), cell.getRow(),convertString(value));} else {label = new Label(cell.getColumn(), cell.getRow(),convertString(value), cellFormat);}sheet.addCell(label);}public void writeToExcel(int sheetNum, int rowNum, int columnNum,Object value, boolean lineWrap) throws BiffException, IOException,WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);this.writeToCell(sheet, rowNum, columnNum, value, lineWrap);}public void writeToExcel(int sheetNum, String columnRowNum, Object value,boolean lineWrap) throws BiffException, IOException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);this.writeToCell(sheet, columnRowNum, value, lineWrap);}public void writeToExcel(String sheetName, String columnRowNum,Object value, boolean lineWrap) throws BiffException, IOException,WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetName);this.writeToCell(sheet, columnRowNum, value, lineWrap);}private void writeToCell(WritableSheet sheet, int rowNum, int columnNum,Object value, boolean lineWrap) throws WriteException,RowsExceededException {WritableCell cell = sheet.getWritableCell(columnNum, rowNum);writeToCell(sheet, cell, value, lineWrap);}private void writeToCell(WritableSheet sheet, String columnRowNum,Object value, boolean lineWrap) throws WriteException,RowsExceededException {WritableCell cell = sheet.getWritableCell(columnRowNum);writeToCell(sheet, cell, value, lineWrap);}private void writeToCell(WritableSheet sheet, WritableCell cell,Object value, boolean lineWrap) throws WriteException,RowsExceededException {CellFormat cellFormat = cell.getCellFormat();Label label;if (cellFormat == null) {label = new Label(cell.getColumn(), cell.getRow(),convertString(value));} else {WritableCellFormat wCellFormat = new WritableCellFormat(cellFormat);wCellFormat.setWrap(lineWrap);label = new Label(cell.getColumn(), cell.getRow(),convertString(value), wCellFormat);}sheet.addCell(label);}public void insertRow(int sheetNum, int rowNum)throws RowsExceededException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);sheet.insertRow(rowNum);}public void insertRowWithFormat(int sheetNum, int rowNum, int columnSize)throws RowsExceededException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);insertRowWithFormat(sheet, rowNum, columnSize);}public void insertRowWithFormat(String sheetName, int rowNum, int columnSize)throws RowsExceededException, WriteException {WritableSheet sheet = this.wWorkBook.getSheet(sheetName);insertRowWithFormat(sheet, rowNum, columnSize);}private void insertRowWithFormat(WritableSheet sheet, int rowNum,int columnSize) throws RowsExceededException, WriteException {sheet.insertRow(rowNum);sheet.setRowView(rowNum, sheet.getRowView(rowNum - 1));for (int i = 0; i < columnSize; i++) {CellFormat cellFormat = sheet.getCell(i, rowNum - 1).getCellFormat();if (cellFormat != null) {Label label = new Label(i, rowNum, "", cellFormat);sheet.addCell(label);}}}public void renameSheet(int sheetNum, String newName) throws IOException {WritableSheet sheet = this.wWorkBook.getSheet(sheetNum);sheet.setName(newName);}public void renameSheet(String oldName, String newName) throws IOException {WritableSheet sheet = this.wWorkBook.getSheet(oldName);sheet.setName(newName);}public void copySheet(int oldSheetNum, String newSheetName) {this.wWorkBook.copySheet(oldSheetNum, newSheetName, oldSheetNum + 1);}public void copySheet(int oldSheetNum, int newSheetNum, String newSheetName) {this.wWorkBook.copySheet(oldSheetNum, newSheetName, newSheetNum);}public void writeAndClose() {if (this.wWorkBook != null) {try {this.wWorkBook.write();} catch (Exception e) {}}if (this.wWorkBook != null) {try {this.wWorkBook.close();} catch (Exception e) {}}if (this.workBook != null) {try {this.workBook.close();} catch (Exception e) {}}}public static void fileCopy(String srcPath, String destPath)throws IOException {File destFile = new File(destPath);File destFileDir = destFile.getParentFile();if (destFileDir != null && !destFileDir.exists()) {destFileDir.mkdirs();}FileChannel srcChannel = new FileInputStream(srcPath).getChannel();FileChannel destChannel = new FileOutputStream(destPath).getChannel();try {srcChannel.transferTo(0, srcChannel.size(), destChannel);} finally {srcChannel.close();destChannel.close();}}public static String replaceHtmlStr(String str) {if (str.indexOf(" ") > -1) {str = str.replaceAll(" ", " ");}if (str.indexOf("<br/>") > -1) {str = str.replaceAll("<br/>", "\r\n");}if (str.indexOf("<br />") > -1) {str = str.replaceAll("<br />", "\r\n");}if (str.indexOf("</a>") > -1) {str = str.replaceAll("</a>", "");}int startIndex = -1;int endIndex = -1;while (str.indexOf("<a href=") > -1) {startIndex = str.indexOf("<a href=");endIndex = str.indexOf(">", startIndex);if (startIndex > -1 && endIndex > -1) {str = str.substring(0, startIndex)+ str.substring(endIndex + 1, str.length());}}return str;}private static String convertString(Object value) {if (value == null) {return "";} else {return value.toString();}}public static void main(String[] args) throws Exception {File file = new File("D:\\chenfeng\\test.xls");ExcelMakerUtil excelMaker = new ExcelMakerUtil(file);try {excelMaker.writeToExcel(0, "J2", "Hello");excelMaker.insertRowWithFormat(0, 9, 61);} finally {excelMaker.writeAndClose();}}}??
?
?
?
?
?