本文介绍了一个Java工具类ExcelUtils，用于实现Excel文件的导入导出功能。支持多种数据类型的转换，包括数字、日期等，并能兼容Excel 2003及2007以上版本。此外，还提供了单元格格式化处理的方法。
package EPF.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletResponse;
import oracle.jbo.Row;
import oracle.jbo.ViewObject;
import org.apache.myfaces.trinidad.model.UploadedFile;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelUtils {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
// private POIFSFileSystem fs;
// private HSSFWorkbook wb;
// private HSSFSheet sheet;
// private HSSFRow row;
public void setFile(UploadedFile _file) {
this._file = _file;
}
public UploadedFile getFile() {
return _file;
}
private UploadedFile _file;
public ExcelUtils() {
super();
}
public void exportExcelFile(FacesContext facesContext,String sheetName,String[] headerName,String[] columnName,ViewObject vo,String fileName){
@SuppressWarnings("oracle.jdeveloper.java.semantic-warning")
FacesContext context = facesContext.getCurrentInstance();
ExternalContext externalContext = context.getExternalContext();
try {
HttpServletResponse response = (HttpServletResponse) externalContext.getResponse();
String afileName = new String(fileName.toString().getBytes("GBK"), "ISO-8859-1");
OutputStream outputStream = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename = " + afileName);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet createSheet = workbook.createSheet(sheetName);
createSheet.setDefaultColumnWidth(10);
createSheet.setColumnWidth(7, 12 * 256);
createSheet.setColumnWidth(8, 12 * 256);
createSheet.setColumnWidth(9, 12 * 256);
createSheet.setColumnWidth(10, 12 * 256);
XSSFFont boldFont = workbook.createFont();
boldFont.setColor(HSSFColor.BLACK.index);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldFont.setFontHeightInPoints((short)10);
XSSFCellStyle boldStyle = workbook.createCellStyle();
boldStyle.setFont(boldFont);
boldStyle.setFillBackgroundColor(HSSFColor.CORNFLOWER_BLUE.BLACK.index);
// XSSFCellStyle style = workbook.createCellStyle();
// style.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
// style.setFillBackgroundColor(HSSFColor.AQUA.index);
// style.setFillPattern(XSSFCellStyle.BIG_SPOTS);
int index = 0;
XSSFRow headerRow = createSheet.createRow(index);
for(int i = 0 ; i < headerName.length ; i++){
XSSFCell cell = headerRow.createCell(i);
cell.setCellValue(headerName[i]);
cell.setCellStyle(boldStyle);
}
index++;
Row[] allRowsInRange = vo.getAllRowsInRange();
for(int k = 0; k < allRowsInRange.length ; k++){
XSSFRow createRow = createSheet.createRow(index);
for(int j = 0; j < columnName.length ; j++){
Object columnValue = allRowsInRange[k].getAttribute(columnName[j]);
XSSFCell cell = createRow.createCell(j);
setConvertedCellValue(workbook,cell,columnValue);
}
index++;
}
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
// XSSFFont createFont = workbook.createFont();
// XSSFCellStyle cellStyle = workbook.createCellStyle();
// XSSFDataFormat format = workbook.createDataFormat();
// cellStyle.setDataFormat(format.getFormat("@"));
} catch (IOException e) {
e.printStackTrace();
}
}
private void setConvertedCellValue(XSSFWorkbook wb, XSSFCell cell, Object value) {
if (value instanceof oracle.jbo.domain.Number) {
oracle.jbo.domain.Number number = (oracle.jbo.domain.Number) value;
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(number.getValue());
}else
if (value instanceof Double) {
Double number = (Double)value;
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(number);
}else
if (value instanceof BigDecimal) {
String number = value.toString();
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(number);
}else
if (value instanceof oracle.jbo.domain.Date) {
oracle.jbo.domain.Date adfdate = (oracle.jbo.domain.Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date;
try {
date = sdf.parse(adfdate.toString());
XSSFCellStyle cellStyle = wb.createCellStyle();
XSSFCreationHelper creationHelper = wb.getCreationHelper();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy"));
//cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyle);
cell.setCellValue(date);
} catch (ParseException e) {
;
}
}else
if (value instanceof String) {
String string = (String)value;
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(string);
}else{
if(value != null && !"".equals(value)){
String string = value.toString();
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(string);
}
}
}
public void import2007ExcelFile(InputStream inputStream){
}
public String[] import2003ExcelFile(InputStream inputStream){
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
//title[i] = getStringCellValue(row.getCell((short) i));
title[i] = getCellFormatValue(row.getCell((short) i));
}
for (String s : title) {
System.out.print(s + " ");
}
Map<Integer, String> map = readExcelContent(inputStream);
System.out.println("获得Excel表格的内容:");
for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
}
return title;
}
/**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
public Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
HSSFWorkbook wb = null;
try {
//fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
str += getCellFormatValue(row.getCell((short) j)).trim() + " ";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
/**
* 根据HSSFCell类型设置数据
* @param cell
* @return
*/
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
//方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
//cellvalue = cell.getDateCellValue().toLocaleString();
//方法2：这样子的data格式是不带带时分秒的：2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public void importFile(){
UploadedFile file = getFile();
if(file == null){
JSFUtils.addFacesErrorMessage("请选择上传文件!");
}
try {
InputStream inputStream = file.getInputStream();
String filename = file.getFilename();
ExcelUtils eu = new ExcelUtils();
if(filename.endsWith("xls")){//03
eu.import2003ExcelFile(inputStream);
}else if(filename.endsWith("xlsx")){//07
eu.import2007ExcelFile(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
try {
// 对读取Excel表格标题测试
InputStream is = new FileInputStream("E:\\123.xls");
System.out.println("IS: " + is);
ExcelUtils excelReader = new ExcelUtils();
// excelReader.import2003ExcelFile(is);
List<List<Object>> listob = excelReader.getBankListByExcel(is, "123.xls");
System.out.println("size:" + listob.size());
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
System.out.println("ID:" + lo.get(0));
System.out.println("父ID:" + lo.get(1));
System.out.println("状态:" + lo.get(2));
System.out.println("标题:" + lo.get(3));
System.out.println("责任部门:" + lo.get(4));
System.out.println("负责人:" + lo.get(5));
System.out.println("提出人:" + lo.get(6));
System.out.println("提出日期:" + lo.get(7));
System.out.println("开始日期:" + lo.get(8));
System.out.println("计划结束:" + lo.get(9));
System.out.println("更新日期:" + lo.get(10));
System.out.println("过程数:" + lo.get(11));
System.out.println("附件数:" + lo.get(12));
System.out.println("排序:" + lo.get(13));
}
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误！");
}
return wb;
}
/**
*
* @param in,fileName
* @return
* @throws IOException
*/
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = this.getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
org.apache.poi.ss.usermodel.Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(this.getCellValue(cell));
}
list.add(li);
}
}
// in.close();
// work.close();
return list;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
public Object getCellValue(Cell cell){
Object value = null;
// DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
// DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
value = dataFormatter.formatCellValue(cell);//格式化数字
//value = (int)cell.getNumericCellValue();
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}else{
value = cell.getNumericCellValue();
System.out.println("123");
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}
excel导入导出
最新推荐文章于 2022-09-29 09:41:05 发布
扫一扫
2273
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
