Excel导入导出
摘要:简单的基于Apache的POI的Excel的导入、导出。仅作基础操作、功能需要的可以自己根据自己的需求添加自己的实现。
一:简介
在实际项目中经常会有关于Excel的操作、从Excel中读取数据导入到数据库、从数据库中导出数据生成Excel表格等等。这里不是一个全套的拿来就能用的工具。而是简单的导入、导出Excel的数据功能、没有牵扯到样式等等。
POI是Apache提供的用于操作Microsoft的office的一套办公软件、比如Word、Excel、ppt等、这里仅仅使用POI操作Excel。
二:具体导入导出
1、首先是去Apache官网下载POI所需要的jar包——网址:http://poi.apache.org/download.html 、最新版本到了3.10。不再详细介绍组件的含义、有兴趣的可以自己看官网上的说明。
2、建立java项目(web项目后面有补充):
3、两个简单导入导出类
a) ExcelReader:
- /**
- * Description:Excel数据读取简单工具类,POI实现,兼容Excel2003,及Excel2007
- **/
- package com.chy.excel.utils;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.ArrayList;
- import java.util.List;
- import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
- import org.apache.poi.ss.usermodel.Cell;
- import org.apache.poi.ss.usermodel.DateUtil;
- import org.apache.poi.ss.usermodel.Row;
- import org.apache.poi.ss.usermodel.Sheet;
- import org.apache.poi.ss.usermodel.Workbook;
- import org.apache.poi.ss.usermodel.WorkbookFactory;
- public class ExcelReader {
- Workbook wb = null;
- List<String[]> dataList = new ArrayList<String[]>(100);
- public ExcelReader(InputStream is) {
- try {
- wb = WorkbookFactory.create(is);
- } catch (InvalidFormatException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- public ExcelReader(String path) {
- try {
- InputStream inp = new FileInputStream(path);
- wb = WorkbookFactory.create(inp);
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (InvalidFormatException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- /**
- * 取Excel所有数据,包含header
- */
- public List<String[]> getAllData(int sheetIndex) {
- int columnNum = 0;
- Sheet sheet = wb.getSheetAt(sheetIndex);
- if (sheet.getRow(0) != null) {
- columnNum = sheet.getRow(0).getLastCellNum()
- - sheet.getRow(0).getFirstCellNum();
- }
- if (columnNum > 0) {
- for (Row row : sheet) {
- String[] singleRow = new String[columnNum];
- int n = 0;
- for (int i = 0; i < columnNum; i++) {
- Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_BLANK:
- singleRow[n] = "";
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- singleRow[n] = Boolean.toString(cell
- .getBooleanCellValue());
- break;
- // 数值
- case Cell.CELL_TYPE_NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)) {
- singleRow[n] = String.valueOf(cell
- .getDateCellValue());
- } else {
- cell.setCellType(Cell.CELL_TYPE_STRING);
- String temp = cell.getStringCellValue();
- // 判断是否包含小数点,如果不含小数点,则以字符串读取,如果含小数点,则转换为Double类型的字符串
- if (temp.indexOf(".") > -1) {
- singleRow[n] = String.valueOf(new Double(temp))
- .trim();
- } else {
- singleRow[n] = temp.trim();
- }
- }
- break;
- case Cell.CELL_TYPE_STRING:
- singleRow[n] = cell.getStringCellValue().trim();
- break;
- case Cell.CELL_TYPE_ERROR:
- singleRow[n] = "";
- break;
- case Cell.CELL_TYPE_FORMULA:
- cell.setCellType(Cell.CELL_TYPE_STRING);
- singleRow[n] = cell.getStringCellValue();
- if (singleRow[n] != null) {
- singleRow[n] = singleRow[n].replaceAll("#N/A", "")
- .trim();
- }
- break;
- default:
- singleRow[n] = "";
- break;
- }
- n++;
- }
- if ("".equals(singleRow[0])) {
- continue;
- }// 如果第一行为空,跳过
- dataList.add(singleRow);
- }
- }
- return dataList;
- }
- /**
- * 返回Excel最大行index值,实际行数要加1
- */
- public int getRowNum(int sheetIndex) {
- Sheet sheet = wb.getSheetAt(sheetIndex);
- return sheet.getLastRowNum();
- }
- /**
- * 返回数据的列数
- */
- public int getColumnNum(int sheetIndex) {
- Sheet sheet = wb.getSheetAt(sheetIndex);
- Row row = sheet.getRow(0);
- if (row != null && row.getLastCellNum() > 0) {
- return row.getLastCellNum();
- }
- return 0;
- }
- /**
- * 获取某一行数据 计数从0开始,rowIndex为0代表header行
- */
- public String[] getRowData(int sheetIndex, int rowIndex) {
- String[] dataArray = null;
- if (rowIndex > this.getColumnNum(sheetIndex)) {
- return dataArray;
- } else {
- dataArray = new String[this.getColumnNum(sheetIndex)];
- return this.dataList.get(rowIndex);
- }
- }
- /**
- * 获取某一列数据
- */
- public String[] getColumnData(int sheetIndex, int colIndex) {
- String[] dataArray = null;
- if (colIndex > this.getColumnNum(sheetIndex)) {
- return dataArray;
- } else {
- if (this.dataList != null && this.dataList.size() > 0) {
- dataArray = new String[this.getRowNum(sheetIndex) + 1];
- int index = 0;
- for (String[] rowData : dataList) {
- if (rowData != null) {
- dataArray[index] = rowData[colIndex];
- index++;
- }
- }
- }
- }
- return dataArray;
- }
- }
b) ExcelWriter:
- package com.chy.excel.bean;
- import java.util.Date;
- public class StudentInfo {
- private int stuId;
- private String stuName;
- private double stuScore;
- private Date birthDay;
- public StudentInfo() {
- super();
- }
- public StudentInfo(int stuId, String stuName, double stuScore, Date birthDay) {
- super();
- this.stuId = stuId;
- this.stuName = stuName;
- this.stuScore = stuScore;
- this.birthDay = birthDay;
- }
- public Date getBirthDay() {
- return birthDay;
- }
- public void setBirthDay(Date birthDay) {
- this.birthDay = birthDay;
- }
- public int getStuId() {
- return stuId;
- }
- public void setStuId(int stuId) {
- this.stuId = stuId;
- }
- public String getStuName() {
- return stuName;
- }
- public void setStuName(String stuName) {
- this.stuName = stuName;
- }
- public double getStuScore() {
- return stuScore;
- }
- public void setStuScore(double stuScore) {
- this.stuScore = stuScore;
- }
- }
c) ExcelWriter:
- package com.chy.excel.utils;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.lang.reflect.Field;
- import java.lang.reflect.Method;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- import java.util.List;
- 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;
- /**
- *
- * @author andyChen
- *
- * @param <T>
- * 数据类型泛型
- */
- @SuppressWarnings("unchecked")
- public class ExcelWriter<T> {
- private String excelSavePath;
- private List<T> list;
- private String excelName;
- private String[] titles;
- /**
- * 构造方法的形式将Excel保存地址、名称、数据传入
- *
- * @param excelSavePath
- * Excel保存地址
- * @param excelName
- * Excel保存名称
- * @param list
- * 写入Excel中的数据
- */
- public ExcelWriter(String excelSavePath, String excelName, List<T> list,
- String[] titles) {
- // 参数校验
- if (list.size() == 0) {
- throw new IllegalArgumentException(
- "the argument of list is illegal");
- }
- // 如果标题与属性个数不匹配、则抛异常。
- Class t = list.get(0).getClass();
- Field[] filed = t.getDeclaredFields();
- if (filed.length != titles.length) {
- throw new IllegalArgumentException(
- "title can not match the context");
- }
- // 初始化数据
- this.excelSavePath = excelSavePath;
- this.list = list;
- this.excelName = excelName;
- this.titles = titles;
- }
- /**
- * 将初始化好的数据写入到Excel中
- * @return 是否写入成功?
- */
- public boolean writerExcel() {
- HSSFWorkbook wb = null;
- OutputStream os = null;
- try {
- wb = new HSSFWorkbook();
- // 可以指定Sheet名称、可以使用默认的
- HSSFSheet sheet = wb.createSheet();
- // 将第一行设置为标题
- HSSFRow titleRow = sheet.createRow(0);
- for (int j = 0; j < titles.length; j++) {
- HSSFCell titleCell = titleRow.createCell(j);
- titleCell.setCellValue(titles[j]);
- }
- // 根据记录数创建行、一条记录对应一行
- for (int i = 0; i < list.size(); i++) {
- // 注意是从第二行开始添加数据、第一行是标题
- HSSFRow row = sheet.createRow(i + 1);
- // 获取list中JavaBean的属性数
- T bean = list.get(i);
- // 反射拿到T中所有方法、包括私有方法
- Class t = bean.getClass();
- Field[] filed = t.getDeclaredFields();
- // 反射获取当前对象每个属性的值、设置到对应的列中。
- for (int j = 0; j < filed.length; j++) {
- HSSFCell cell = row.createCell(j);
- Field field = filed[j];
- String fieldName = field.getName();
- String getMethodNameByFieldName = "get"
- + fieldName.substring(0, 1).toUpperCase()
- + fieldName.substring(1);
- Method method = t.getMethod(getMethodNameByFieldName);
- Object value = method.invoke(bean);
- setValue(value, cell);
- }
- }
- os = new FileOutputStream(excelSavePath + "/" + excelName + ".xls");
- wb.write(os);
- // 会先执行finally中代码、然后再执行return。
- return true;
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- // 关闭流
- if (os != null) {
- try {
- os.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- return false;
- }
- /**
- * 设置value值、可以按照这种形式来处理自己想处理的value。
- *
- * @param value
- * 要写入Excel的值
- * @param cell
- * value被写入的行
- */
- private void setValue(Object value, HSSFCell cell) {
- String textValue = value.toString();
- if (value instanceof Date) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- textValue = sdf.format(value);
- }
- cell.setCellValue(textValue);
- }
- }
4、测试类——Client:
- package com.chy.excel.client;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.util.ArrayList;
- import java.util.Arrays;
- import java.util.Date;
- import java.util.List;
- import com.chy.excel.bean.StudentInfo;
- import com.chy.excel.utils.ExcelReader;
- import com.chy.excel.utils.ExcelWriter;
- public class Client {
- private static String excelSavePath = "F:";
- private static String excelName = "student_information";
- public static void main(String[] args) {
- System.out.println(exportExcel());
- importExcel();
- }
- private static void importExcel(){
- //ExcelReader reader = new ExcelReader("F:\\student_information.xls"); 效果一样。
- ExcelReader reader;
- try {
- reader = new ExcelReader(new FileInputStream("F:\\student_information.xls"));
- List<String[]> list = reader.getAllData(0);
- for (String[] data : list) {
- System.out.println(Arrays.toString(data));
- }
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- }
- }
- /**
- * 导出Excel
- *
- * @return 导出成功?
- */
- private static boolean exportExcel() {
- String[] titles = { "学号", "姓名", "分数", "出生日期" };
- List<StudentInfo> list = new ArrayList<StudentInfo>();
- list.add(new StudentInfo(1, "chy", 90.3, new Date()));
- list.add(new StudentInfo(2, "mxx", 70.3, new Date()));
- ExcelWriter<StudentInfo> ew = new ExcelWriter<StudentInfo>(
- excelSavePath, excelName, list, titles);
- return ew.writerExcel();
- }
- }
三:补充
项目中许多时候都是在web项目中来使用Excel的、但是从上面可以知道、java项目与web项目的区别无非就是环境不一样、我们对与Excel的操作、只需知道关于他的流或者文件在服务器上的存放的位置即可。
在web项目、比如ssh项目总、Excel的导入就可以按照普通的文件上传的方式来处理、jsp页面一个form表单、有个type为file的input标签。提交之前校验一下是不是Excel文件、是的话让他提交、我们在struts2的Action中定义一个同名的File类型的私有属性、给他getXXX、setXXX方法、这样、struts2就会将文件上传到服务器、我们通过File来获取流还不是 a pice of cake?下面就是java一样的操作了。
下载的话就是使用数据构造Excel、完成之后和常见下载文件一样、提供流、使用Response流输出就ok。