jxl示例
创建xls文件：
public static void testJxmWrite(String[] args) {
WritableWorkbook workbook = null;
try {
// 创建工作薄
workbook = Workbook.createWorkbook(new File("example.xls"));
// 创建工作表
WritableSheet sheet = workbook.createSheet("工作表", 0);
// 添加表头
Label name = new Label(0, 0, "用户名");
sheet.addCell(name);
Label amount = new Label(1, 0, "金额");
sheet.addCell(amount);
Label date = new Label(2, 0, "时间");
sheet.addCell(date);
// 添加数据
sheet.addCell(new Label(0, 1, "admin"));
sheet.addCell(new jxl.write.Number(1, 1, 1000));
DateFormat customDateFormat = new DateFormat(
"yyyy年MM月dd日 HH:mm:ss");
WritableCellFormat dateFormat = new WritableCellFormat(
customDateFormat);
sheet.addCell(new DateTime(2, 1, new Date(), dateFormat));
// 写入文件
workbook.write();
}
catch (IOException | WriteException e) {
LOGGER.error("创建文件出错", e);
}
finally {
if (workbook != null) {
if (workbook != null) {
try {
workbook.close();
}
catch (Throwable t) {
LOGGER.error("关闭workbook出错");
}
}
}
}
}读取xls文件:
public static void testJxlRead(String[] args) {
Workbook workbook = null;
try {
// 读取工作薄
workbook = Workbook.getWorkbook(new File("example.xls"));
// 读取工作表
Sheet sheet = workbook.getSheet(0);
for (int i=0; i<sheet.getRows(); i++) {
for (int j=0; j<sheet.getColumns();j++) {
System.out.print(sheet.getCell(j, i).getContents());
System.out.print("\t");
}
System.out.println();
}
}
catch (IOException | BiffException e) {
LOGGER.error("读取文件出错", e);
}
finally {
if (workbook != null) {
if (workbook != null) {
try {
workbook.close();
}
catch (Throwable t) {
LOGGER.error("关闭workbook出错");
}
}
}
}
}POI示例
创建xlsx文件
public static void testPoiWrite() {
try (OutputStream fileOut = new FileOutputStream("example.xlsx")) {
// 创建gongzuob
try (Workbook wb = new XSSFWorkbook()) {
// 创建工作表
Sheet sheet = wb.createSheet("new sheet");
CreationHelper createHelper = wb.getCreationHelper();
// 创建标题行
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("用户名");
row.createCell(1).setCellValue("金额");
row.createCell(2).setCellValue("时间");
// 添加数据行
row = sheet.createRow(1);
row.createCell(0).setCellValue("admin");
row.createCell(1).setCellValue("1000");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat()
.getFormat("yyyy-MM-dd HH:mm:ss"));
Cell cell = row.createCell(2);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
wb.write(fileOut);
}
}
catch (IOException e) {
LOGGER.error("创建文件出错", e);
}
}读取xlsx文件
public static void testPoiRead(String[] args) {
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try (Workbook wb = WorkbookFactory.create(new File("example.xlsx"))) {
for (Sheet sheet : wb) {
for (Row row : sheet) {
for (Cell cell : row) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(
cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print(
sdf.format(cell.getDateCellValue()));
}
else {
System.out.print(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.print(cell.getCellFormula());
break;
default:
System.out.print("");
break;
}
System.out.print("\t");
}
System.out.println();
}
}
}
catch (EncryptedDocumentException | InvalidFormatException
| IOException e) {
LOGGER.error("读取文件出错", e);
}
}
