Excel工具类 古天山
import java.io.Serializable;
import java.util.List;
public class ExcelData implements Serializable {
private static final long serialVersionUID = 4444017239100620999L;
// 表头
private List<String> titles;
// 数据
private List<List<Object>> rows;
// 页签名称
private String name;
public List<String> getTitles() {
return titles;
}
public void setTitles(List<String> titles) {
this.titles = titles;
}
public List<List<Object>> getRows() {
return rows;
}
public void setRows(List<List<Object>> rows) {
this.rows = rows;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}导出
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.net.URLEncoder;
import java.util.List;
public class ExportExcelUtils {
public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
exportExcel(data, response.getOutputStream());
}
public static void exportExcel(ExcelData data, ServletOutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
try {
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet1";
}
XSSFSheet sheet = wb.createSheet(sheetName);
writeExcel(wb, sheet, data);
wb.write(out);
} finally {
wb.close();
}
}
private static void writeExcel(XSSFWorkbook wb, XSSFSheet sheet, ExcelData data) {
int rowIndex = 0;
rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
autoSizeColumns(sheet, data.getTitles().size() + 1);
}
private static int writeTitlesToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {
int rowIndex = 0;
int colIndex = 0;
Font titleFont = wb.createFont();
titleFont.setFontName("simsun");
titleFont.setBold(true);
// titleFont.setFontHeightInPoints((short) 14);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// titleStyle.setFillForegroundColor(new XSSFColor(new Color(9, 247, 247)));
titleStyle.setFillForegroundColor(new XSSFColor(new Color(237, 247, 242)));
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
XSSFRow titleRow = sheet.createRow(rowIndex);
// titleRow.setHeightInPoints(25);
colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}
private static int writeRowsToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex = 0;
Font dataFont = wb.createFont();
dataFont.setFontName("simsun");
// dataFont.setFontHeightInPoints((short) 14);
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
dataStyle.setFont(dataFont);
setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
for (List<Object> rowData : rows) {
XSSFRow dataRow = sheet.createRow(rowIndex);
// dataRow.setHeightInPoints(25);
colIndex = 0;
for (Object cellData : rowData) {
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
colIndex++;
}
rowIndex++;
}
return rowIndex;
}
private static void autoSizeColumns(XSSFSheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
}
}导入
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ImportExcelUtil{
private final static String Excel_2003 = ".xls"; //2003 版本的excel
private final static String Excel_2007 = ".xlsx"; //2007 版本的excel
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作簿
Workbook work = this.getWorkbook(in, fileName);
if(work == null) {
throw new Exception("创建Excel工作簿为空！");
}
Sheet sheet = null;
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中的所有行
//int totalRow = sheet.getPhysicalNumberOfRows();//如果excel有格式，这种方式取值不准确
int totalRow = sheet.getPhysicalNumberOfRows();
for(int j = sheet.getFirstRowNum(); j<totalRow; j++) {
row = sheet.getRow(j);
if(!isRowEmpty(row)) {
//if(row != null && !"".equals(row)) {
//获取第一个单元格的数据是否存在
Cell fristCell=row.getCell(0);
if(fristCell!=null){
//遍历所有的列
List<Object> li = new ArrayList<Object>();
//int totalColum = row.getLastCellNum();
for(int y = row.getFirstCellNum(); y<row.getLastCellNum(); y++) {
cell = row.getCell(y);
String callCal = this.getCellValue(cell)+"";
li.add(callCal);
}
list.add(li);
}
}else if(isRowEmpty(row)){
continue;
}
}
}
in.close();
return list;
}
/**
* 描述：根据文件后缀，自动适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
* */
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception {
Workbook work = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(Excel_2003.equals(fileType)){
work=new HSSFWorkbook(inStr);//2003 版本的excel
}else if(Excel_2007.equals(fileType)) {
work=new XSSFWorkbook(inStr);//2007 版本的excel
}else {
throw new Exception("解析文件格式有误！");
}
return work;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
* */
public Object getCellValue(Cell cell) {
/*Object value = null;
DecimalFormat df1 = new DecimalFormat("0.00");//格式化number，string字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");//日期格式化
DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
if(cell !=null && !"".equals(cell)) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df1.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
}else if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = cell.getDateCellValue();
value = sdf.format(date);
}
else {
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
}
return value;*/
String result = new String();
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA: //Excel公式
try {
result = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
result = String.valueOf(cell.getRichStringCellValue());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("M月d日");
double value = cell.getNumericCellValue();
Date date = DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#.##");
}
result = format.format(value);
}
break;
case HSSFCell.CELL_TYPE_STRING:// String类型
result = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
default:
result = "";
break;
}
return result;
}
public String getFormat(String str) {
if(str.equals("null")) {
str="";
return str;
}else{
return str;
}
}
/**
* 判断行是否为空
* @param row
* @return
*/
public static boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
return false;
}
return true;
}
}