package com.sccf.common.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* 描述:
* 导出excel
*
* @author W
* @create 2018-11-02 15:46
*/
public class ExportExcelUtil<T> {
public void ExportWithResponse(String title, String fileName,
String[] titleName,String[] columnName, List<T> dataList,
HttpServletResponse response) throws Exception {
XSSFWorkbook wb = createXssfWorkBook();
Sheet sheet = createSheet(wb, title, titleName);
CellStyle cellStyle = createCellStyle(wb);
for (int i = 0; i < dataList.size(); i++) {
Row row= sheet.createRow(i+2);
Object obj = dataList.get(i);
Class clazz = obj.getClass();
//创建单元格并设置单元格内容
for (int k = 0; k < columnName.length; k++) {
Cell cell = row.createCell(k);
Field field = clazz.getDeclaredField(columnName[k]);
//设置对象的访问权限,保证对private的属性的访问
field.setAccessible(true);
Object o = field.get(obj);
if(null!=o){
if(o instanceof Date){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cell.setCellValue(sdf.format(o));
}else if(o instanceof BigDecimal){
cell.setCellValue(String.valueOf(((BigDecimal) o).setScale(2,BigDecimal.ROUND_CEILING)));
}else if(o instanceof Integer) {
cell.setCellValue(o.toString());
} else if (o instanceof Float) {
cell.setCellValue(o.toString());
} else if (o instanceof Double) {
cell.setCellValue(o.toString());
} else if (o instanceof Long) {
cell.setCellValue(o.toString());
}else{
cell.setCellValue(String.valueOf(o));
}
cell.setCellStyle(cellStyle);
}
}
}
downWrite(response,wb,fileName);
}
//创建HSSFWorkbook(2003)
public HSSFWorkbook createHssfWorkBook(){
HSSFWorkbook wb = new HSSFWorkbook();
return wb;
}
//创建XSSFWorkbook(2007)
public XSSFWorkbook createXssfWorkBook(){
XSSFWorkbook xb = new XSSFWorkbook();
return xb;
}
//创建sheet页面,标题
public Sheet createSheet(Workbook xb,String sheetName,String [] titleName) {
//建立新的sheet对象(excel的表单)
Sheet sheet = xb.createSheet(sheetName);
sheet.setDefaultColumnWidth(17); //设置默认列宽,实际上回多出2个字符
sheet.setDefaultRowHeight((short) (2 * 256)); //设置默认行高,表示2个字符的高度
Row row1 = sheet.createRow(0);
Cell cell = row1.createCell(0);
cell.setCellValue(sheetName);
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,titleName.length-1));
cell.setCellStyle(createCellStyle(xb,1));
//在sheet里创建第二行标题
Row row2 = sheet.createRow(1);
CellStyle cellStyle = createCellStyle(xb, 2);
for (int j = 0; j < titleName.length; j++) {
Cell cell2 = row2.createCell(j);
cell2.setCellValue(titleName[j]);
cell2.setCellStyle(cellStyle);
}
return sheet;
}
public CellStyle createCellStyle(Workbook xb){
return createCellStyle(xb,3);
}
//单元格样式
public CellStyle createCellStyle(Workbook xb,int i){
CellStyle cellStyle = xb.createCellStyle();
Font fontStyle = xb.createFont();
if(i==1){
fontStyle.setFontHeightInPoints((short) 16);
}else if(i==2){
fontStyle.setFontHeightInPoints((short) 12);
fontStyle.setBold(true);
}else{
fontStyle.setFontHeightInPoints((short) 10);
}
cellStyle.setFont(fontStyle);
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
cellStyle.setWrapText(true);//自动换行
return cellStyle;
}
//下载
public void downWrite(HttpServletResponse response,Workbook xb,String fileName)throws Exception{
if(xb instanceof HSSFWorkbook){
fileName += ".xls";
}
if(xb instanceof XSSFWorkbook){
fileName += ".xlsx";
}
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" .concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
OutputStream out = response.getOutputStream();
try {
xb.write(out);// 将数据写出去
} finally {
out.close();
}
}
}
Java 导入excel数据
最新推荐文章于 2024-07-03 14:13:18 发布