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";
 
    @Autowired
    private IConfigDubboService configService;
 
    @Resource
    private 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")
    @ResponseBody
    public 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> warnlist) throws ClassCastException{
        HSSFRow row =null;
        HSSFCellStyle style = wb.createCellStyle();
        style.setWrapText(true);
        for (List