最近做试题导入导出,选用了poi导入和导出excel文件,直接用poi的API感觉代码很混乱,耦合度很高,所以封装了两个底层类。一个是ExcelReader:主要包含读取excel内容的方法;另一个是ExcelWriter:主要包含几个生成excel文件的方法。现贴出来供大家以后参考使用:
1、ExcelWriter.java
package com.eruite.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* @author caihua
*/
public class ExcelReader {
private HSSFWorkbook wb = null;// book [includes sheet]
private HSSFSheet sheet = null;
private HSSFRow row = null;
private int sheetNum = 0; // 第sheetnum个工作表
private int rowNum = 0;
private FileInputStream fis = null;
private File file = null;
public ExcelReader() {
}
public ExcelReader(File file) {
this.file = file;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
public void setSheetNum(int sheetNum) {
this.sheetNum = sheetNum;
}
public void setFile(File file) {
this.file = file;
}
/**
* 读取excel文件获得HSSFWorkbook对象
*/
public void open() throws IOException {
fis = new FileInputStream(file);
wb = new HSSFWorkbook(new POIFSFileSystem(fis));
fis.close();
}
/**
* 返回sheet表数目
*
* @return int
*/
public int getSheetCount() {
int sheetCount = -1;
sheetCount = wb.getNumberOfSheets();
return sheetCount;
}
/**
* sheetNum下的记录行数
*
* @return int
*/
public int getRowCount() {
if (wb == null)
System.out.println("=============>WorkBook为空");
HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 读取指定sheetNum的rowCount
*
* @param sheetNum
* @return int
*/
public int getRowCount(int sheetNum) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 得到指定行的内容
*
* @param lineNum
* @return String[]
*/
public String[] readExcelLine(int lineNum) {
return readExcelLine(this.sheetNum, lineNum);
}
/**
* 指定工作表和行数的内容
*
* @param sheetNum
* @param lineNum
* @return String[]
*/
public String[] readExcelLine(int sheetNum, int lineNum) {
if (sheetNum < 0 || lineNum < 0)
return null;
String[] strExcelLine = null;
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(lineNum);
int cellCount = row.getLastCellNum();
strExcelLine = new String[cellCount + 1];
for (int i = 0; i <= cellCount; i++) {
strExcelLine[i] = readStringExcelCell(lineNum, i);
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelLine;
}
/**
* 读取指定列的内容
*
* @param cellNum
* @return String
*/
public String readStringExcelCell(int cellNum) {
return readStringExcelCell(this.rowNum, cellNum);
}
/**
* 指定行和列编号的内容
*
* @param rowNum
* @param cellNum
* @return String
*/
public String readStringExcelCell(int rowNum, int cellNum) {
return readStringExcelCell(this.sheetNum, rowNum, cellNum);
}
/**
* 指定工作表、行、列下的内容
*
* @param sheetNum
* @param rowNum
* @param cellNum
* @return String
*/
public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
if (sheetNum < 0 || rowNum < 0)
return "";
String strExcelCell = "";
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(rowNum);
if (row.getCell((short) cellNum) != null) { // add this condition
// judge
switch (row.getCell((short) cellNum).getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
strExcelCell = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
strExcelCell = String.valueOf(row.getCell((short) cellNum)
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
strExcelCell = row.getCell((short) cellNum)
.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
strExcelCell = "";
break;
default:
strExcelCell = "";
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelCell;
}
public static void main(String args[]) {
File file = new File("C:\\qt.xls");
ExcelReader readExcel = new ExcelReader(file);
try {
readExcel.open();
} catch (IOException e) {
e.printStackTrace();
}
readExcel.setSheetNum(0); // 设置读取索引为0的工作表
// 总行数
int count = readExcel.getRowCount();
for (int i = 0; i <= count; i++) {
String[] rows = readExcel.readExcelLine(i);
for (int j = 0; j < rows.length; j++) {
System.out.print(rows[j] + " ");
}
System.out.print("\n");
}
}
}
2、ExcelWriter.java
package com.eruite.util;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 生成导出Excel文件对象
*
* @author caihua
*
*/
public class ExcelWriter {
// 设置cell编码解决中文高位字节截断
private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;
// 定制浮点数格式
private static String NUMBER_FORMAT = "#,##0.00";
// 定制日期格式
private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
private OutputStream out = null;
private HSSFWorkbook workbook = null;
private HSSFSheet sheet = null;
private HSSFRow row = null;
public ExcelWriter() {
}
/**
* 初始化Excel
*
*/
public ExcelWriter(OutputStream out) {
this.out = out;
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet();
}
/**
* 导出Excel文件
*
* @throws IOException
*/
public void export() throws FileNotFoundException, IOException {
try {
workbook.write(out);
out.flush();
out.close();
} catch (FileNotFoundException e) {
throw new IOException(" 生成导出Excel文件出错! ", e);
} catch (IOException e) {
throw new IOException(" 写入Excel文件出错! ", e);
}
}
/**
* 增加一行
*
* @param index
* 行号
*/
public void createRow(int index) {
this.row = this.sheet.createRow(index);
}
/**
* 获取单元格的值
*
* @param index
* 列号
*/
public String getCell(int index) {
HSSFCell cell = this.row.getCell((short) index);
String strExcelCell = "";
if (cell != null) { // add this condition
// judge
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
strExcelCell = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
strExcelCell = String.valueOf(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
strExcelCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
strExcelCell = "";
break;
default:
strExcelCell = "";
break;
}
}
return strExcelCell;
}
/**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, int value) {
HSSFCell cell = this.row.createCell((short) index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
}
/**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, double value) {
HSSFCell cell = this.row.createCell((short) index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
}
/**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, String value) {
HSSFCell cell = this.row.createCell((short) index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(XLS_ENCODING);
cell.setCellValue(value);
}
/**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, Calendar value) {
HSSFCell cell = this.row.createCell((short) index);
cell.setEncoding(XLS_ENCODING);
cell.setCellValue(value.getTime());
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
}
public static void main(String[] args) {
System.out.println(" 开始导出Excel文件 ");
File f = new File("C:\\qt.xls");
ExcelWriter e = new ExcelWriter();
try {
e = new ExcelWriter(new FileOutputStream(f));
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
e.createRow(0);
e.setCell(0, "试题编码 ");
e.setCell(1, "题型");
e.setCell(2, "分值");
e.setCell(3, "难度");
e.setCell(4, "级别");
e.setCell(5, "知识点");
e.createRow(1);
e.setCell(0, "t1");
e.setCell(1, 1);
e.setCell(2, 3.0);
e.setCell(3, 1);
e.setCell(4, "重要");
e.setCell(5, "专业");
try {
e.export();
System.out.println(" 导出Excel文件[成功] ");
} catch (IOException ex) {
System.out.println(" 导出Excel文件[失败] ");
ex.printStackTrace();
}
}
}
分享到:
相关推荐
Apache POI导入和导出Excel文件
SpringBoot +Mybatis +POI导入、导出Excel文件 简单清晰几个类,处理SpringBoot 导入导出excel doc目录下 是mysql数据库脚本 和 导入的模板
java_poi实现excel导入导出功能,有详细的注解
poi excel 导入 导出poi excel 导入 导出poi excel 导入 导出poi excel 导入 导出poi excel 导入 导出poi excel 导入 导出
Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出Java POI EXCEL导入导出...
文件包含了 1.POI导入导出的工具类; 2.图片URL导出成图片文件; 3.导出excel和图片压缩包格式文件。 如果下载后有任何问题,可以联系我哦!
poi导入/导出Excel表格,合并单元格的读取和设置
SpringBootMybatis+poi+Thymeleaf实现excel文件数据导入到数据库以及从数据库将数据导出成excel
java实现excel的导入导出(poi详解),经过测试poi效率要比jxl要高很多,特别是数据量大的时候jxl根本无法用肉眼入目,本资源是个可运行项目demo,很有参考价值!
利用POI 编写的一个Excel表导入导出实例 代码有注释
使用POI导入导出Excel2003、2007示例,相关jar辅助 https://my.oschina.net/u/658145/blog/268112
这是一个Excel表格导入与导出功能,Excel美化,并且添加了进度条,丢上Eclipse直接运行使用,附带测试数据与说明。Java项目使用maven搭建。
基于maven实现POI实现导入导出功能模板案例,简单的例子,让人更加容易理解,学习起来更加方便,以实现自定义导出excel模板
概述:Java poi导入导出EXCEL工具类(兼容各版本) 一、功能说明 允许同时导入或导出多个sheet,同一sheet可同时存在多个数据块,按数据块划分处理数据。 二、配置文件示例及详细说明 1、导入xml配置示例如下(见...
SpringBoot整合poi实现Excel文件的导入和导出,其中单独分装出一个ExcelFormatUtil工具类来实现对单元格数据格式进行判断。
POI导入导出excel文件 --- 个人珍藏笔记,一看就会用
poi_excel导入导出各种excel文件实例
前几天项目上需要加上导入导出,在网上找了一下,么有...于是自己写,源码里所需要的jar都有,从jsp界面进行Excel的导入导出,并可用于上传和下载。如有所用,喜不自禁,分享,开源的意义。 写的不是特别好,不喜勿喷!