java Excel 自用开发模板

下载导出

import com.hpay.admin.api.vo.Message;
import com.hpay.admin.dubbo.IConfigDubboService;
import com.hpay.admin.dubbo.IFileExportLogDubboService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.Map;/*** 文件公共操作* @author Garcia*/@Controller
@RequestMapping("/file")
@Slf4j
public class FileCommonController {private static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");private static final String TXT = "txt";private static final String CSV = "csv";private static final String XLS = "xls";private static final String XLSX = "xlsx";@Autowiredprivate IConfigDubboService configService;@Resourceprivate IFileExportLogDubboService fileExportLogDubboService;/*** 下载Excel** @param response* @param excName*/@RequestMapping("downExcel")public void downExcel(HttpServletResponse response, String excName,String fileType,String downFileName) {if (StringUtils.isBlank(excName)) {log.warn("文件名为空");return;}if (StringUtils.isBlank(fileType)) {log.warn("文件类型为空");return;}String path = configService.getProperty("tempExcelPath");String fileName = downFileName + "-" + excName;if (TXT.equals(fileType)||CSV.equals(fileType)){writeTxt(response,excName,path,fileName);}else if(XLS.equals(fileType) || XLSX.equals(fileType)){writeExcel(response,excName,path,fileName);}}private void writeTxt(HttpServletResponse response, String excName,String path,String fileName){//设置文件路径File file = new File(path + FILE_SEPARATOR + excName);if (file.exists()) {response.setContentType("application/binary; charset=UTF-8");response.setCharacterEncoding("UTF-8");try {response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName,StandardCharsets.UTF_8));} catch (UnsupportedEncodingException e) {log.error("其他错误！", e);}byte[] buffer = new byte[1024];FileInputStream fis = null;BufferedInputStream bis = null;try {fis = new FileInputStream(file);bis = new BufferedInputStream(fis);OutputStream os = response.getOutputStream();int i = bis.read(buffer);while (i != -1) {os.write(buffer, 0, i);i = bis.read(buffer);}} catch (Exception e) {log.error("写出文本文件错误！", e);} finally {if (bis != null) {try {bis.close();} catch (IOException e) {log.error("关闭流错误！", e);}}if (fis != null) {try {fis.close();} catch (IOException e) {log.error("关闭流错误！", e);}}delexcel(excName);}}}private void writeExcel(HttpServletResponse response, String excName,String path,String fileName){File file = new File(path + FILE_SEPARATOR + excName);if (file.exists()) {HSSFWorkbook wb = null;try {InputStream is = new FileInputStream(file);wb = new HSSFWorkbook(is);} catch (Exception e) {log.error("读取Excel文件错误！", e);}response.setContentType("application/binary; charset=UTF-8");response.setCharacterEncoding("UTF-8");try {response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName,StandardCharsets.UTF_8));} catch (UnsupportedEncodingException e) {log.error("其他错误！", e);}OutputStream os = null;try {os = response.getOutputStream();if (wb != null) {wb.write(os);}if (os != null) {os.flush();}} catch (IOException e) {log.error("写出Excel文件错误！", e);} finally {if (os != null) {try {os.close();} catch (IOException e) {log.error("关闭流错误！", e);}}delexcel(excName);}}}/*** 删除服务器Excel文件** @param excName* @return*/@RequestMapping("delExcel")@ResponseBodypublic Message delexcel(String excName) {String path = configService.getProperty("tempExcelPath");try{File file = new File(path + FILE_SEPARATOR + excName);long len = file.length();Thread.sleep(3000);if (len!=file.length()){return Message.error("当前文件正在操作，请稍后再删");}file.delete();fileExportLogDubboService.deleteByName(excName);}catch (Exception e){log.error("文件删除异常",e);}return Message.success();}

生成Excel

String []titles = new String[]{"",""};HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet=generateContentSheet(wb,titles);createRow(wb,sheet,rowList);private void createRow(HSSFWorkbook wb,HSSFSheet sheet, List<List<Object>> warnlist) throws ClassCastException{HSSFRow row =null;HSSFCellStyle style = wb.createCellStyle();style.setWrapText(true);for (List<Object> value : warnlist) {row = sheet.createRow(sheet.getLastRowNum() + 1);for (int i = 0; i < value.size(); i++) {Cell cell = row.createCell(i);cell.setCellStyle(style);if(value.get(i) instanceof String){cell.setCellValue((String)value.get(i));}else if(value.get(i) instanceof Integer){cell.setCellValue((Integer)value.get(i));}else if(value.get(i) instanceof Double){cell.setCellValue((Double)value.get(i));}else if(value.get(i) instanceof Boolean){cell.setCellValue((Boolean)value.get(i));}else if(value.get(i) instanceof Date){cell.setCellValue((Date)value.get(i));}else if(value.get(i) instanceof Calendar){cell.setCellValue((Calendar)value.get(i));}else if(value.get(i) instanceof RichTextString){cell.setCellValue((RichTextString)value.get(i));}else if(value.get(i) instanceof Long){cell.setCellValue((Long)value.get(i));}else if(value.get(i) instanceof BigDecimal){cell.setCellValue(value.get(i).toString());}else if(value.get(i)==null){cell.setCellValue("");}else{log.error("不支持导出类型:{},{}",value.get(i).getClass(),value.get(i));throw new ClassCastException("不支持导出类型:"+value.get(i).getClass()+","+value.get(i));}}}}private HSSFSheet generateContentSheet(HSSFWorkbook workbook,String[] titles){HSSFSheet sheet = null;sheet = workbook.createSheet("审评报告");HSSFRow row = sheet.createRow(0);sheet.autoSizeColumn(0);sheet.setColumnWidth(0,sheet.getColumnWidth(0)*17/10);sheet.autoSizeColumn(1);sheet.setColumnWidth(1,sheet.getColumnWidth(1)*27/10);sheet.autoSizeColumn(2);sheet.setColumnWidth(2,sheet.getColumnWidth(2)*17/10);sheet.autoSizeColumn(3);sheet.setColumnWidth(3,sheet.getColumnWidth(3)*17/10);sheet.autoSizeColumn(4);sheet.setColumnWidth(4,sheet.getColumnWidth(4)*17/10);CellStyle style;Font headerFont = workbook.createFont();
//        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);style = createBorderedStyle(workbook);
//        style.setAlignment(CellStyle.ALIGN_CENTER);
//        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
//        style.setFillPattern(BorderStyle.SOLID_FOREGROUND);
//        style.setFont(headerFont);for (int i = 0; i < titles.length; i++) {HSSFCell cell = row.createCell(i);cell.setCellValue(titles[i]);cell.setCellStyle(style);}return sheet;}/*** 生产单元格样式* @param wb* @return*/private static CellStyle createBorderedStyle(Workbook wb) {CellStyle style = wb.createCellStyle();
//        style.setBorderRight(BorderStyle.THIN);
//        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
//        style.setBorderBottom(BorderStyle.THIN);
//        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
//        style.setBorderLeft(BorderStyle.THIN);
//        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
//        style.setBorderTop(BorderStyle.THIN);
//        style.setTopBorderColor(IndexedColors.BLACK.getIndex());return style;}