import java.io.File;import java.util.Date;import jxl.Sheet;import jxl.Workbook;import jxl.format.UnderlineStyle;import jxl.write.DateFormat;import jxl.write.DateTime;import jxl.write.Label;import jxl.write.NumberFormat;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableImage;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.Number;import jxl.write.Boolean;
public class ExcelParse ...{
WritableWorkbook wwk;
public WritableWorkbook getWritableWorkbook() ...{ return wwk;
} public void setWritableWorkbook(WritableWorkbook wwk) ...{ this.wwk = wwk; } public WritableWorkbook newWritableWorkbook(String fileName) throws Exception ...{ WritableWorkbook wwk = Workbook.createWorkbook(new File(fileName)); return wwk; } public void addSheet(String sheetName, int index) throws Exception ...{ wwk.createSheet(sheetName, index); // wwk.write();//若增加此一行,则只能创建第一个sheet } public void removeSheet(int index) throws Exception ...{ wwk.removeSheet(index); } public void setLabel(WritableSheet sheet, int column, int row, String value) throws WriteException ...{ Label labelCell = new Label(column, row, value); sheet.addCell(labelCell); } // 格式化输入字符串 public void setFormatLabel(WritableSheet sheet, int column, int row, String value, WritableFont writableFont) throws WriteException ...{ // WritableFont writableFont=new // writableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, // UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED); WritableCellFormat writableCellFormat = new WritableCellFormat( writableFont); Label labelCell = new Label(column, row, value, writableCellFormat); sheet.addCell(labelCell); } public void setNumber(WritableSheet sheet, int column, int row, double value) throws WriteException ...{ Number numCell = new Number(column, row, value); sheet.addCell(numCell); // this.setFormatNumber(sheet, column, row, value, null);//行不通,报空指针异常 } // 格式化输入数字 public void setFormatNumber(WritableSheet sheet, int column, int row, double value, String format) throws WriteException ...{ NumberFormat numberFormat = new NumberFormat(format); WritableCellFormat cellFormat = new WritableCellFormat(numberFormat); Number numCell = new Number(column, row, value, cellFormat); sheet.addCell(numCell); } public void setBoolean(WritableSheet sheet, int column, int row, boolean value) throws WriteException ...{ Boolean boolCell = new Boolean(column, row, value); sheet.addCell(boolCell); } public void setDateTime(WritableSheet sheet, int column, int row, Date value) throws WriteException ...{ DateTime dateCell = new DateTime(column, row, value); sheet.addCell(dateCell); } // 格式化输入日期 public void setFormatDateTime(WritableSheet sheet, int column, int row, Date value, String format) throws WriteException ...{ DateFormat dateFormat = new DateFormat(format); WritableCellFormat cellFormat = new WritableCellFormat(dateFormat); DateTime dateCell = new DateTime(column, row, value, cellFormat); sheet.addCell(dateCell); } public void setImage(WritableSheet sheet, int fromColumn, int fromRow, int toColumn, int toRow, String imageUri) throws WriteException ...{ File f = new File(imageUri); WritableImage imageCell = new WritableImage(fromColumn, fromRow,toColumn,toRow, f); sheet.addImage(imageCell); } public Workbook getWorkbook(String fileName) throws Exception ...{ Workbook wkb = Workbook.getWorkbook(new File(fileName)); return wkb; } public Sheet[] getSheets() ...{ return wwk.getSheets(); } public void close() throws Exception ...{ wwk.write(); this.wwk.close(); } public static void main(String[] args) throws Exception ...{ ExcelParse ep = new ExcelParse(); ep.setWritableWorkbook(ep.newWritableWorkbook("create.xls")); ep.addSheet("firstSheet", 0); ep.addSheet("thirdSheet", 1);// index不一定是一系列连接数 ep.addSheet("secondSheet", 1);// 位置第二位,后面的后移一位 ep.removeSheet(0); WritableSheet sheet = (WritableSheet) ep.getSheets()[0]; int column = 2, row = 2; ep.setBoolean(sheet, column++, row++, false); ep.setDateTime(sheet, column++, row++, new Date()); ep.setLabel(sheet, column++, row++, "String label"); ep.setNumber(sheet, column++, row++, 3.1415926); ep.setImage(sheet, 5, 10, 10, 20, "sunset.png"); ep.close(); // Workbook workbook=ep.getWorkbook("create.xls"); }
}疑问:如果只修改Excel中的一小部分该如何?
import java.io.File;import jxl.CellType;import jxl.Workbook;import jxl.write.Label;import jxl.write.WritableCell;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class ExcelUpdate ...{ public static void main(String[] args)throws Exception...{ Workbook rwb=Workbook.getWorkbook(new File("create.xls")); WritableWorkbook wwb=Workbook.createWorkbook(new File("b.xls"),rwb);//把create.xls复制到b.xls WritableSheet ws=wwb.getSheet(0); WritableCell wc=ws.getWritableCell(2,2); if(wc.getType()==CellType.LABEL)...{ Label label=(Label)wc; label.setString("The value has been modified"); //不需要这行,不然异常:Cell has already been added to a worksheet //ws.addCell(label); } wwb.write(); wwb.close(); rwb.close(); }}