使用JXL读写文件
一、使用JXL读EXCEL
1、读取EXCEL截图:
2、步骤说明
(1)获取文件
(2)获取文件的WorkBook
(3)获取要读取的SHEET,SHEET下标从0开始
(4)首先确定参数,要读多少行与多少列,可以用程序自动获取,也可以手动控制,使用FOR循环遍历。
3、具体程序代码
/**
* 使用JXL读Excel
*/
public static void readExcelData(){
File file = new File("D:/test.xls"); //1、获取文件
Workbook book = null;
try {
book = Workbook.getWorkbook(file); //2、获取Workbook
Sheet sheet = book.getSheet(1); //3、获取第 2 个SHEET
StringBuilder sb = null;
Cell[] cells = null;
//遍历行 从第2行开始遍历到第8行 注意行索引从0开始
for (int i = 1; i < 8; i++) {
cells = sheet.getRow(i); //该列所有值
if(cells.length == 0){ //如果该列没有值 就continue;
continue;
}
sb = new StringBuilder("");
//遍历列 从第1列开始遍历到第3列 注意列索引从0开始
for (int j = 0; j < 3; j++) {
int index = cells[j].getColumn();//获取该列的列号 为数值
String key = NumberUtils.toLetterString(index+1); //将数值转换成对应的A,B,C...
String value = cells[j].getContents().trim();//获取单元格的值
String cellType = cells[j].getCellFormat().getFormat().getFormatString().toUpperCase(); //获取单元格样式即格式编码
sb.append(value+",");
}
//省略了存储在List中的过程
sb.deleteCharAt(sb.lastIndexOf(","));
System.out.println(sb.toString());
}
book.close(); //关闭book
} catch (Exception e) {
e.printStackTrace();
}
}
4、执行结果:
5、经验:
<1>存储读取的数据,可以使用Map存储。每一行数据都是一个Map,key为列号即A,B,C…value就为单元格的值。如果有多行,就需要在创建一个List,封装这些Map,list的size即行数。完整结构为List<Map<String,String>> 但List为无序的,如果你要将数据保存到数据库中,最后要回写到EXCEL中,建议Map中除了保存单元格值以外,再保存一个行号。最后获取的时候用行号排序即可。如果不牵扯到数据库,可以使用其他有序集合。
<2>获取数据后,往往要对数据的真假以及单元格格式做校验,此时,要规定一些约束。因为excel中的单元格格式太多,用程序不好控制,所以必须约束为某些固定的格式。
<3>如果excel中的sheet很多,并且保存数据库要求sheet与后台表结构一一对应,那么需要根据excel模版去创建表机构,就需要有模版管理的模版去完成配置,利用前端配置生成建表SQL。此时,可以顺便拼接入库的SQL和查询的SQL。再需要再插入或者查询时,直接使用即可。
6、补充
使用JXL读取单元格公式:
if(cells[j].getType() == CellType.NUMBER_FORMULA ||
cells[j].getType() == CellType.STRING_FORMULA ||
cells[j].getType() == CellType.BOOLEAN_FORMULA ||
cells[j].getType() == CellType.DATE_FORMULA ||
cells[j].getType() == CellType.FORMULA_ERROR){
FormulaCell fCell = (FormulaCell)cells[j];
System.out.println(fCell.getFormula());
二、使用JXL写EXCEL
1、 效果截图:
向excel的sheet中写入以下数据:
2、 步骤说明:
(1) 定义两个文件,一个是源文件,一个是副本文件。副本文件是用于写的文件。(JXL不允许对源文件做操作,允许创建副本做操作)。
(2) 打开源文件,创建一个副本文件。
(3) 打开或者创建一个SHEET。
(4) 做add操作。如果新增的单元格数值有固定格式,需要创建对应样式。
3、 具体代码实现
/**
* 向excel写
*/
public static void writeExcelTest(){
WritableWorkbook book = null; //打开一个EXCEL
Workbook wb = null;
File file = new File("D:/test.xls");
File file2 = new File("D:/test_jxl_writer.xls");
try {
wb = Workbook.getWorkbook(file);
// 打开一个文件的副本,并且指定数据写回到原文件
book = Workbook.createWorkbook(file2, wb);
WritableSheet sheet = book.createSheet("jxl_wirter", 2);//getSheet(0);
//常规
sheet.addCell(new Label(1,0,"aaa")); //参数:列,行,值 下标从0开始 以下类似
//日期
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date=sdf.parse("2012-07-09");
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-mm-dd");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 1,date,wcfDF);
sheet.addCell(labelDTF);
//百分比
NumberFormat nfPERCENT_FLOAT = new jxl.write.NumberFormat("0.00%");
WritableCellFormat wcfnfPERCENT_FLOAT = new jxl.write.WritableCellFormat(nfPERCENT_FLOAT);
jxl.write.Number numSalerate = new jxl.write.Number(
1,
2,
Double.parseDouble("12.6") * 0.01,
wcfnfPERCENT_FLOAT);
sheet.addCell(numSalerate);
//会计专用
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0.00");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number number = new jxl.write.Number(1, 3,Double.parseDouble("55.3"),wcfN);
sheet.addCell(number);
//整数
jxl.write.NumberFormat nf2 = new jxl.write.NumberFormat("0");
jxl.write.WritableCellFormat wcfN2 = new jxl.write.WritableCellFormat(nf2);
jxl.write.Number number2 = new jxl.write.Number(1, 4,Double.parseDouble("10.00"),wcfN2);
sheet.addCell(number2);
book.write();//写
wb.close();//关闭流
book.close();//关闭流
} catch (Exception e) {
e.printStackTrace();
}
}
4、 经验:
使用jxl写excel有一些限制。因为jxl已经没有人维护了,所以版本较低,bug也多,好处是用来读excel还是不错的选择,效率比poi要高。但写文件有一些落后,比如不支持excel宏,所以写的文件里如果含有宏,多半会报错的。所以写excel建议使用POI会更好一些。
使用POI读写文件
一、使用POI读写EXCEL
1、 前言
项目中没有使用POI读excel文件。只用来写了,所以读和写的代码和案例放在一起了,其实在使用POI操作excel的时候,读和写可以说是一体的。既可以一边读也可以一边写。因为POI操作excel的前提是将EXCEL放在内存中操作的,既然已经在内存中,我们既可以读也可以写。不过也因为如此,如果excel文件过大,容易报内存溢出的问题。
当遇到该问题时,如果是单元测试,注意修改启动参数,如果是服务器报错,就修改服务器的初始内存大小:
单元测试解决方法如图:
2、 实现目标:
3、 步骤流程:
(1) 创建获取源文件和目标文件
(2) 获取源文件的FileInputStream流
(3) 获取源文件的PoiFSFileSystem流
(4) 获取源文件的HSSFWorkBook
(5) 遍历行列(下标从0开始)使用具体方法写入
(6) 遇到有样式的单元格需要创建样式并设置
(7) 写入到FileOutputStream指定的目标文件。关闭流
4、 具体代码
/**
* 生成Excel文件
* @throws IOException
*/
public static void newExcel(String fileDir) throws IOException{
HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
wb.createSheet("业务状况表");//建立新的sheet对象
wb.createSheet("损益表");//建立新的sheet对象
wb.createSheet("Sheet3");//建立新的sheet对象
// 生成文件
FileOutputStream fileOut = new FileOutputStream(fileDir);
wb.write(fileOut);
fileOut.close();
}
/**
* 保存数据到excel
* @param fileDir
* @param list
*/
public static void saveToExcel(String fileDir,List<String> list){
FileOutputStream fos = null;
FileInputStream fis = null;
POIFSFileSystem poiFS = null;
HSSFWorkbook wb = null;
try {
fis = new FileInputStream(fileDir);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
poiFS = new POIFSFileSystem(fis);
} catch (IOException e) {
e.printStackTrace();
}
try {
wb = new HSSFWorkbook(poiFS);
} catch (IOException e) {
e.printStackTrace();
}
String[] titles = {"编号","教师名称","薪水"};
HSSFFont font_bold = wb.createFont(); //字体
font_bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
HSSFCellStyle titleStyle= wb.createCellStyle();
titleStyle.setFont(font_bold);
HSSFSheet sheet = null;
if(titles != null && titles.length > 0){
sheet = wb.getSheetAt(0);
HSSFRow titleRow = sheet.createRow(0);
for(int i=0;i<titles.length;i++){
sheet.setColumnWidth((short)i, (short)(25 * 256));
HSSFCell cell = titleRow.createCell((short)i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(titles[i]));
cell.setCellStyle(titleStyle);
}
}
Object[] strs = new Object[]{"1","小鹏老师",8000};
HSSFRow rows;
int rownum = 1; //起始行
for(int i=0;i<1;i++){//list.size()
rows = sheet.createRow(rownum ++);
// Object obj = list.get(i);
// str = list.get(i);
// strs = (Object[])obj;
for(int j=0;j<strs.length;j++){//第n行的列值
HSSFCell cell_id = rows .getCell((short)j);
cell_id = rows.createCell((short)j);
if(j == 2){
BigDecimal db = null;
try {
db = new BigDecimal(strs[j].toString(),java.math.MathContext.UNLIMITED);
} catch (Exception e) {
System.out.println("NUMBER转换异常:"+"-452456465.93");
e.printStackTrace();
}
cell_id.setCellValue(db.doubleValue());
HSSFDataFormat hssfDF = wb.createDataFormat();
HSSFCellStyle accountantStyle = wb.createCellStyle();
accountantStyle.setDataFormat(hssfDF.getFormat("_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);(@_)")); //jxl
// accountantStyle.setDataFormat(hssfDF.getFormat("_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)")); //poi
cell_id.setCellStyle(accountantStyle);
}else{
cell_id.setCellType(HSSFCell.CELL_TYPE_STRING);
cell_id.setCellValue(new HSSFRichTextString(strs[j].toString()));
}
}
}
try {
fos = new FileOutputStream(fileDir);
try {
wb.write(fos);
} catch (IOException e) {
e.printStackTrace();
}
try {
fis.close();
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally{
try {
fis.close();
fis = null;
fos.close();
fos = null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
5、 优化代码:
虽然我们实现了代码,但其实我们的代码中存在一定的缺陷,再上面的代码中,在我们写入工资一列的时候,我们创建了该列的样式为会计专用。假设这里我们要循环10000次,那么这个样式我们就需要创建10000次,不讨论性能怎么样,这种写法本身就是不合理的,写入到excel中也会报错,尤其是对低版本的excel,容易出现单元格格式丢失的问题,所以在这里我们需要优化我们的代码。
在写入过程中,难免要创建单元格对应的样式,其实这些样式无非就是单元格的格式例如会计专用、整数、日期等,既然这些样式都是比较固定和统一的,那么我们可以在for循环以外提前创建,放入在Map中,在使用的时候直接get出来就可以了。部分代码如下:
wb = new HSSFWorkbook(poiFS);
if(wb != null){
HSSFDataFormat hssfDF = wb.createDataFormat();
HSSFCellStyle accountStyle = wb.createCellStyle(); //会计专用
// accountStyle.setDataFormat(hssfDF.getFormat("_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \"-\"_ ;_ @_ ")); //jxl读取后的会计专用格式
accountStyle.setDataFormat(hssfDF.getFormat("_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)")); //poi写入后为会计专用
styleMap.put("accountStyle", accountStyle);
}
另外:上述代码中我们发现当我们回写会计专用的时候,使用的样式字符串分两种,一个是jxl使用的,一个是poi使用的,两者对这类格式的读取和识别略微不同。在使用不同jar的时候建议分开使用。