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<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;
    }

文章来源地址https://www.uudwc.com/A/DN9aR/

原文地址:https://blog.csdn.net/Qensq/article/details/132947779

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请联系站长进行投诉反馈,一经查实,立即删除!

h
上一篇 2023年09月18日 04:01
下一篇 2023年09月18日 04:03