相关jar包:jxl
使用pom配置导入
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
Excel的导出
示例代码
@Test
public void testOut() throws Exception {
//1,创建excel对象
WritableWorkbook excel = Workbook.createWorkbook(new File("out.xls"));
//2,创建Sheet对象(名称,位置)
WritableSheet sheet = excel.createSheet("第一个Sheet", 0);
//-------------------------普通单元格---------------------------
//设置字体的格式
WritableFont wf = new WritableFont(WritableFont.ARIAL);
wf.setColour(Colour.BLUE);
//设置单元格的格式
WritableCellFormat wcf = new WritableCellFormat(wf);
//设置背景颜色
wcf.setBackground(Colour.RED);
//设置水平居中
wcf.setAlignment(Alignment.CENTRE);
//设置竖直居中
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//3,建立单元格(列,行,内容,格式)
Label cell = new Label(0, 0, "第一个单元格", wcf);
//--------------------------日期单元格-----------------------------------
//设置日期单元格的格式
WritableCellFormat st = new WritableCellFormat(new DateFormat("yyyy-MM-dd hh:mm:ss"));
st.setVerticalAlignment(VerticalAlignment.CENTRE);
st.setAlignment(Alignment.CENTRE);
//创建日期单元格对象
DateTime cellDate = new DateTime(6, 6, new Date(), st);
//-------------------------------------------------------------
//4,把单元格添加进sheet中
sheet.addCell(cell);
sheet.addCell(cellDate);
//设置列和行的大小
sheet.setColumnView(0, 50);
sheet.setColumnView(6, 50);
sheet.setRowView(0, 500);
//合并单元格(左上角坐标(列,行),右下角坐标列,行))
sheet.mergeCells(0, 0, 2, 2);
//5,导出
excel.write();
//6,关闭流
excel.close();
}
Excel文件内容:
Excel的导入
Excel文件内容:
示例代码:
@Test
public void readExcel() throws Exception {
Workbook wb = Workbook.getWorkbook(new File("H:\\IDEA_WorkSpace\\CRM\\read.xls"));
Sheet sheet = wb.getSheet(0);
int cols = sheet.getColumns();//总列数
int rows = sheet.getRows();//总行数
for (int i = 0; i < rows; i++) {
for (int j = 0; j < cols; j++) {
//根据列-行获取每个单元格对象
Cell cell = sheet.getCell(j, i);
//日期类型的处理
if (cell.getType().equals(CellType.DATE)) {
DateCell dateCell = (DateCell) sheet.getCell(j, i);
Date jxlDate = dateCell.getDate();
//设置时间格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
//设置时区(东八区)
sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
System.out.println(sdf.format(jxlDate));
} else {
//打印普通单元格的内容
System.out.print(cell.getContents() + "\t");
}
}
System.out.println();
}
}
注:jxl无法对后缀为.xlsx的Excel文件导入,只能导入.xls格式:会报:
jxl.read.biff.BiffException: Unable to recognize OLE stream异常