package com.cando.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
/**
* poi工具类
*
* @author sec
*
*/
@Component
public class ExcelUtils {
//@Value("${excel.url}")
private String EXCEL_URL="G://excelFile/";// 保存路径
public String getEXCEL_URL() {
return EXCEL_URL;
}
public void setEXCEL_URL(String eXCEL_URL) {
EXCEL_URL = eXCEL_URL;
}
/**
* 创建excel
*
* @param excelName
* 表格文件名
* @param sheetName
* sheet页名
* @param titles
* String[] 表头
* @param list
* List<HashMap<String, Object>>
* @param mappings
* String[] HashMap key
*/
@SuppressWarnings("deprecation")
public void createExcel(String excelName, String sheetName,
String[] titles, List<Map<String, Object>> list, String[] mappings) {
// 第一步,创建一个webbook,对应一个Excel文件
@SuppressWarnings("resource")
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth((short) 15);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
HSSFCell cell = null;
int rows = titles.length;
for (int i = 0; i < rows; i++) {
cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(style);
}
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((int) i + 1);
Map<String, Object> obj = list.get(i);
// 第四步,创建单元格,并设置值
for (int j = 0; j < mappings.length; j++) {
// System.out.println(obj.get(mappings[j]));
if(obj.get(mappings[j])==null){
row.createCell(j).setCellValue("");
}else{
row.createCell(j).setCellValue(obj.get(mappings[j]).toString());
}
}
}
// 第六步,将文件存到指定位置
try {
File f = new File(EXCEL_URL);
if(!f.exists()){
f.mkdirs();
}
FileOutputStream fout = new FileOutputStream(EXCEL_URL + excelName
+ ".xls"); // 系统中excel保存路径
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 文件下载
*
* @param fileName
* @param fileType
* @param response
* @return
* @throws Exception
*/
public boolean downLoadFile(String fileName, String fileType,
HttpServletResponse response) throws Exception {
File file = new File(EXCEL_URL + fileName + ".xls"); // 根据文件路径获得File文件
// 设置文件类型(这样设置就不止是下Excel文件了,一举多得)
if (fileType == null) {
fileType = "xls";
}
if ("pdf".equals(fileType)) {
response.setContentType("application/pdf;charset=GBK");
} else if ("xls".equals(fileType)) {
// response.setContentType("application/msexcel;charset=GBK");
response.setContentType("application/x-excel;charset=UTF-8");
} else if ("doc".equals(fileType)) {
response.setContentType("application/msword;charset=GBK");
}
// 文件名
response.setHeader("Content-Disposition", "attachment;filename=\""
+ new String((fileName + ".xls").getBytes(), "ISO8859-1")
+ "\"");
response.setContentLength((int) file.length());
byte[] buffer = new byte[4096];// 缓冲区
BufferedOutputStream output = null;
BufferedInputStream input = null;
try {
output = new BufferedOutputStream(response.getOutputStream());
input = new BufferedInputStream(new FileInputStream(file));
int n = -1;
// 遍历,开始下载
while ((n = input.read(buffer, 0, 4096)) > -1) {
output.write(buffer, 0, n);
}
output.flush(); // 不可少
response.flushBuffer();// 不可少
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭流,不可少
if (input != null)
input.close();
if (output != null)
output.close();
file.delete();
}
return false;
}
}
推荐大家一个Java的学习网站:Java知识学习网,Java资料下载,Java学习路线图,网址:https://www.java1010.com