package com.bytecode.openexcel.util;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
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 MapExcel {
public static void main(String[] args) {
String step = "";
Workbook workBook = null;
Cell a = null;
String aStr = "";
try {
step = "read the xls";
workBook = Workbook.getWorkbook(new File("c:/myfile.xls"));
Sheet sheet = workBook.getSheet(0);
// 按行读取sheet中的数据
int skipLine = 0; // 可设置跳地读取的行数
for (int i = skipLine; i < sheet.getRows(); i++) {
Cell[] cells = sheet.getRow(i);
for (int j = 0; j < cells.length; j++) {
a = sheet.getCell(j, i);
aStr = a.getContents();
System.out.println(aStr);
}
}
// 按列读取sheet中的数据
for (int i = 0; i < sheet.getColumns(); i++) {
Cell[] cells = sheet.getColumn(i);
for (int j = 0; j < cells.length; j++) {
a = sheet.getCell(i, j);
aStr = a.getContents();
System.out.println(aStr);
}
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
workBook.close();
}
step = "write the xls";
WritableWorkbook workBook1 = null;
try {
workBook1 = Workbook.createWorkbook(new File("c:/output.xls"));
WritableSheet sheet1 = workBook1.createSheet("First sheet", 0);
Label label = new Label(0, 2, "A label record");
sheet1.addCell(label);
Number number = new Number(3, 4, 3.1459);
sheet1.addCell(number);
step = "add formated cell to xls";
// Create a cell format for Arial 10 point font
WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10);
WritableCellFormat arial10format = new WritableCellFormat(
arial10font);
// Create the label, specifying content and format
// 通过一种格式创建单元格
Label label2 = new Label(1, 0, "Arial 10 point label",
arial10format);
sheet1.addCell(label2);
// Cell formats objects are shared, so many cells may use the same
// format object, eg.
// 创建的单元格与前面创建的使用同样的格式
Label label3 = new Label(2, 0, "Another Arial 10 point label",
arial10format);
sheet1.addCell(label3);
// 可以使用其它的构造函数为某一个单元格单独列一种格式
// Create a cell format for Times 16, bold and italic
WritableFont times16font = new WritableFont(WritableFont.TIMES, 16,
WritableFont.BOLD, true);
WritableCellFormat times16format = new WritableCellFormat(
times16font);
// Create the label, specifying content and format
Label label4 = new Label(3, 0, "Times 16 bold italic label",
times16format);
sheet1.addCell(label4);
// 相应的还有对数字和日期的格式,具体看JAVA EXCEL API的指南文件
} catch (RowsExceededException e1) {
e1.printStackTrace();
} catch (WriteException e1) {
e1.printStackTrace();
} catch (IOException e1) {
e1.printStackTrace();
} finally {
try {
workBook1.write();
workBook1.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
step = "copy and modify xls";
WritableWorkbook copy = null;
try {
Workbook workBook2 = Workbook
.getWorkbook(new File("c:/myfile.xls"));
// 把myfile.xls中的内容(包括所有sheet)复制到out.xls中
copy = Workbook.createWorkbook(new File("c:/out.xls"), workBook2);
WritableSheet sheet3 = copy.getSheet(1);
WritableCell cell3 = sheet3.getWritableCell(1, 2);
if (cell3.getType() == CellType.LABEL) {
Label l = (Label) cell3;
l.setString("modify cell");
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
copy.write();
copy.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
JAVA操作Excel Api
最新推荐文章于 2021-03-29 07:46:08 发布