以前项目中用过Excel导入导出过2003版本的,最近研究了一下能够兼容07+ 版本的操作方法。
首先下载POI的jar包,官方网站: http://jakarta.apache.org/poi/,在项目中导入jar包,如下:
下面直接上代码
1.创建Workbook工厂类
package com.songhn.poi.util;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author songhn
*
* 创建文档
*
*/
public class WorkbookFactory {
private Workbook workBook = null;
private String excelFilePath = null;
/**
* 创建文档WorkBook(读文档)
* @param excelFilePath
* @return
* @throws Exception
*/
public Workbook create(String excelFilePath) throws Exception{
this.excelFilePath = excelFilePath;
File file = new File(this.excelFilePath);
FileInputStream fis = new FileInputStream(file);
//2003版的Excel
if(excelFilePath.endsWith(".xls")){
workBook = new HSSFWorkbook(fis);
//2007版以后的Excel
}else if(excelFilePath.endsWith(".xlsx")){
workBook = new XSSFWorkbook(fis);
}
return workBook;
}
/**
* 创建文档WorkBook(写文档)
* @param excelFilePath
* @return
* @throws Exception
*/
public Workbook create(String fileName , boolean flag) throws Exception{
this.excelFilePath = fileName;
//2003版的Excel
if(excelFilePath.endsWith(".xls")){
workBook = new HSSFWorkbook();
//2007版以后的Excel
}else if(excelFilePath.endsWith(".xlsx")){
workBook = new XSSFWorkbook();
}
return workBook;
}
}
2.导入Excel文档
/** * */ package com.songhn.poi.util; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; /** * @author songhn * * 读取Excel工具类(兼容2003以及以后的版本Excel) * */ public class ExcelReadUtil { /** * 读取每一个Sheet的数据 * * @param workBook * @return */ public Map<String, Object> readSheet(Workbook workBook) { Map<String, Object> sheetMap = new HashMap<>(); int _sheetNUM = workBook.getNumberOfSheets(); for (int i = 0; i < _sheetNUM; i++) { Sheet sheet = workBook.getSheetAt(i); String sheetName = sheet.getSheetName(); List<String[]> list = null; if (null != sheet) { list = readRow(sheet); } sheetMap.put(sheetName, list); } return sheetMap; } /** * 读取每一行数据 * * @param workBook * @return */ public List<String[]> readRow(Sheet sheet) { // 获取到最后一行的的行号, 行号是从0开始的 int _rowNum = sheet.getLastRowNum(); List<String[]> list = new ArrayList<String[]>(); for (int i = 0; i <= _rowNum; i++) { Row row = sheet.getRow(i); String[] obj = null; if (null != row) { obj = readCells(row); } list.add(obj); } return list; } /** * 读取每一行的单元格数据 * * @param row * @return */ private String[] readCells(Row row) { int _cellCount = row.getPhysicalNumberOfCells(); String[] values = new String[_cellCount]; for (int i = 0; i < _cellCount; i++) { Cell cell = row.getCell(i); if (null != cell) { String value = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: value = "" + cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: // 判断是否是日期格式数据 if (DateUtil.isCellDateFormatted(cell)) { value = DateUtil.formatDate(cell.getDateCellValue(),"yyyy-MM-dd"); } else { value = "" + cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: value = "" + cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: value = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; default: } values[i] = value; } } return values; } }
3.导出Excel文档
接口:
package com.songhn.poi.inter; import java.util.List; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; /** * 写Excel文档 * @author songhn * */ public interface ExceWriteInter { /** * 创建Excel文档 * @param excelFilePath */ public void createExcel(Workbook workBook,List<String[]> list)throws Exception; }
2003实现:
/**
*
*/
package com.songhn.poi.util;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Workbook;
import com.songhn.poi.inter.ExceWriteInter;
/**
* @author songhn
*
*/
public class ExcelWriteUtil2003 implements ExceWriteInter {
@Override
public void createExcel(Workbook workBook,List<String[]> list)throws Exception {
HSSFSheet sheet = (HSSFSheet) workBook.createSheet();
RichTextString richString = null;
Font font = null;
CellStyle style = null;
for (int i = 0; i < list.size(); i++) {
if(i == 0){
font = workBook.createFont();
font.setFontHeightInPoints((short)24); //字体大小
font.setFontName("楷体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体
font.setColor(HSSFColor.GREEN.index); //绿字
style = workBook.createCellStyle();
style.setFont(font);
String[] values = (String[])list.get(i);
HSSFRow row = (HSSFRow) sheet.createRow(i);
for (int j = 0; j < values.length; j++) {
String string = values[j];
HSSFCell cell = row.createCell(j);
richString = new HSSFRichTextString(string);
cell.setCellValue(richString);
cell.setCellStyle(style);
}
}else{
String[] values = (String[])list.get(i);
HSSFRow row = (HSSFRow) sheet.createRow(i);
for (int j = 0; j < values.length; j++) {
String string = values[j];
HSSFCell cell = row.createCell(j);
richString = new HSSFRichTextString(string);
cell.setCellValue(richString);
}
}
}
}
}
2007+实现:
/**
*
*/
package com.songhn.poi.util;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import com.songhn.poi.inter.ExceWriteInter;
/**
* @author songhn
*
*/
public class ExcelWriteUtil2007 implements ExceWriteInter {
@Override
public void createExcel(Workbook workBook,List<String[]> list)throws Exception {
XSSFSheet sheet = (XSSFSheet) workBook.createSheet();
RichTextString richString = null;
Font font = null;
CellStyle style = null;
for (int i = 0; i < list.size(); i++) {
//设置表头
if(i == 0){
font = workBook.createFont();
font.setFontHeightInPoints((short)12); //字体大小
font.setFontName("微软雅黑");
font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体
font.setColor(HSSFColor.GREEN.index); //绿字
String[] values = (String[])list.get(i);
XSSFRow row = (XSSFRow) sheet.createRow(i+1);
style = workBook.createCellStyle();
style.setFont(font);
style.setBorderBottom(CellStyle.BORDER_DOUBLE);
for (int j = 0; j < values.length; j++) {
String string = values[j];
XSSFCell cell = row.createCell(j+1);
richString = new XSSFRichTextString(string);
cell.setCellValue(richString);
cell.setCellStyle(style);
}
}else{
font = workBook.createFont();
font.setFontHeightInPoints((short)12); //字体大小
font.setFontName("微软雅黑");
style = workBook.createCellStyle();
style.setFont(font);
style.setBorderBottom(CellStyle.BORDER_THIN);
String[] values = (String[])list.get(i);
XSSFRow row = (XSSFRow) sheet.createRow(i+1);
for (int j = 0; j < values.length; j++) {
String string = values[j];
XSSFCell cell = row.createCell(j+1);
richString = new XSSFRichTextString(string);
cell.setCellValue(richString);
cell.setCellStyle(style);
}
}
}
}
}
用到的工具类:
package com.songhn.poi.util;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
/**
* @author songhn
*
* 日期工具
*/
public class DateUtil {
/**
* 判断是否是日期格式数据
* @param cell
* @return
*/
public static boolean isCellDateFormatted(Cell cell){
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return true;
}
return false;
}
/**
* 将日期格式的数据按照预定的格式进行转换
* @param date
* @param formatGeshi
* @return
*/
public static String formatDate(Date date , String formatGeshi){
SimpleDateFormat format =new SimpleDateFormat(formatGeshi);
return format.format(date);
}
}
测试代码:
/**
*
*/
package com.songhn.poi.test;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.ss.usermodel.Workbook;
import com.songhn.poi.util.ExcelReadUtil;
import com.songhn.poi.util.WorkbookFactory;
/**
* @author songhn
*
*/
public class ReadExcelTest {
/**
* @param args
*/
public static void main(String[] args) {
String excelPath = "E:\\testExcel.xls";
WorkbookFactory factory = new WorkbookFactory();
Map map = null;
try {
Workbook workBook= factory.create(excelPath);
ExcelReadUtil util = new ExcelReadUtil();
map = util.readSheet(workBook);
} catch (Exception e) {
e.printStackTrace();
}
Set set = map.keySet();
for(Iterator it=set.iterator();it.hasNext();){
String key = (String)it.next();
System.out.println("Sheet名称:" + key);
List<String[]> list = (List<String[]>)map.get(key);
for (int i = 0; i < list.size(); i++) {
String[] str = list.get(i);
String obj = "";
for (int j = 0; j < str.length; j++) {
obj = obj + str[j] + ",";
}
System.out.println("第"+(i+1)+"行:"+obj);
}
}
}
}
package com.songhn.poi.test;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Workbook;
import com.songhn.poi.inter.ExceWriteInter;
import com.songhn.poi.util.ExcelWriteUtil2003;
import com.songhn.poi.util.ExcelWriteUtil2007;
import com.songhn.poi.util.WorkbookFactory;
public class WriteExcelTest {
/**
* @param args
*/
public static void main(String[] args) {
List<String[]> list = new ArrayList<String[]>();
String[] title = new String[5];
title[0] = "标题1";
title[1] = "标题2";
title[2] = "标题3";
title[3] = "标题4";
title[4] = "标题5";
list.add(title);
for (int i = 1; i < 10; i++) {
String[] str = new String[5];
str[0] = "张三"+i;
str[1] = "李四"+i;
str[2] = "王五"+i;
str[3] = "赵六"+i;
str[4] = "冯七"+i;
list.add(str);
}
ExceWriteInter write = new ExcelWriteUtil2007();
String excelFilePath = "E:\\";
String excelFileName = "test.xlsx";
try {
WorkbookFactory workBookFactory =new WorkbookFactory();
Workbook workbook = workBookFactory.create(excelFileName,false);
write.createExcel(workbook, list);
workbook.write(new FileOutputStream(new File(excelFilePath+excelFileName)));
System.out.println("创建成功!!!");
} catch (Exception e) {
System.out.println("创建失败!!!");
e.printStackTrace();
}
}
}