jxl读写excel常见操作搜集整理2008-04-23 17:26package common.excelTool;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFeatures;
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;
public class WriteExcelTest {
static HashMap map = new HashMap();
public static void main(String[] args) {
try {
// copyDateFormat(new File("c:\\a.xls"), 0, "c:\\copy of a.xls");
// writeExcelUseFormat("c:\\format.xls","test");
// buildNewFormTemplete(new File("c:/templete.xls"),new File( "c:/buildNewFormTemplete.xls"));
// modifyDirectly1(new File("c:/templete.xls"));
// modifyDirectly2(new File("c:/templete.xls"));
copyDateAndFormat(new File("c:/a.xls"), 0, "c:/a2.xls");
} catch (Exception e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
public static void modifyDirectly2(File inputFile) throws Exception{
Workbook w1 = Workbook.getWorkbook(inputFile);
WritableWorkbook w2 = Workbook.createWorkbook(inputFile, w1);
WritableSheet sheet = w2.getSheet(0);
WritableCell cell = null;
CellFormat cf = null;
//加粗
cell = sheet.getWritableCell(0,0);
WritableFont bold = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.BOLD);
cf = new WritableCellFormat(bold);
cell.setCellFormat(cf);
//设置下划线
cell = sheet.getWritableCell(0,1);
WritableFont underline = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE);
cf = new WritableCellFormat(underline);
cell.setCellFormat(cf);
//直截添加可以覆盖掉
setCellValueDirectly(sheet,sheet.getCell(0, 2),new Double(4),CellType.NUMBER);
w2.write();
w2.close();
}
public static void modifyDirectly1(File file) {
try{
// Excel获得文件
Workbook wb = Workbook.getWorkbook(file);
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(file,
wb);
WritableSheet sheet0 = book.getSheet(0);
sheet0.addCell(new Label(0, 1, "陈小稳"));
// 添加一个工作表
WritableSheet sheet = book.createSheet( " 第二页 " , 1 );
sheet.addCell( new Label( 0 , 0 , " 第二页的测试数据 " ));
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
public static void buildNewFormTemplete(File inputFile, File outputFile){
try{
// Excel获得文件
Workbook wb = Workbook.getWorkbook(inputFile);
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(outputFile,
wb);
WritableSheet sheet0 = book.getSheet(0);
sheet0.addCell(new Label(0, 1, "陈小稳"));
// 添加一个工作表
WritableSheet sheet = book.createSheet( " 第二页 " , 1 );
sheet.addCell( new Label( 0 , 0 , " 第二页的测试数据 " ));
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
public static void copyDateAndFormat(File inputFile, int inputFileSheetIndex, String outputFilePath) throws Exception {
Workbook book = null;
Cell cell = null;
//1.避免乱码的设置
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh","CN");
setting.setLocale(locale);
setting.setEncoding("ISO-8859-1");
book = Workbook.getWorkbook(inputFile, setting);
Sheet readonlySheet = book.getSheet(inputFileSheetIndex);
OutputStream os=new FileOutputStream(outputFilePath);//输出的Excel文件URL
WritableWorkbook wwb = Workbook.createWorkbook(os);//创建可写工作薄
WritableSheet writableSheet = wwb.createSheet(readonlySheet.getName(), 0);//创建可写工作表
//2.誊写不同数据格式的数据
for (int rowIndex = 0; rowIndex < readonlySheet.getRows(); rowIndex++) {
for (int colIndex = 0; colIndex < readonlySheet.getColumns(); colIndex++) {
cell = readonlySheet.getCell(colIndex, rowIndex);
//A2B2为合并的单元格,A2有内容,B2为空
// if(colIndex == 0 && rowIndex == 1){
// System.out.println(colIndex + "," + rowIndex + " type:" + cell.getType() +" :" + cell.getContents());
// }
//【有各种设置格式】
if(cell.getType() == CellType.DATE || cell.getType() == CellType.DATE_FORMULA){
writableSheet.addCell(
new jxl.write.DateTime(
colIndex
, rowIndex
, ((DateCell)cell).getDate(),
new jxl.write.WritableCellFormat(
cell.getCellFormat()
)
)
);
}else if(cell.getType() == CellType.NUMBER || cell.getType() == CellType.NUMBER_FORMULA){
writableSheet.addCell(
new jxl.write.Number(
colIndex
, rowIndex
, ((jxl.NumberCell)cell).getValue(),
new jxl.write.WritableCellFormat(
cell.getCellFormat()
)
)
);
}else if(cell.getType() == CellType.EMPTY){
//空的以及合并单元格中第一列外的
// System.out.println("EMPTY:"+cell.getContents());
// System.err.println("空单元格 at " + colIndex + "," + rowIndex +" content:" + cell.getContents());
}else if(cell.getType() == CellType.LABEL || cell.getType() == CellType.STRING_FORMULA){
writableSheet.addCell(
new Label(