封装通用简单excel导出

最新推荐文章于 2023-02-27 16:43:22 发布

三思微糖

最新推荐文章于 2023-02-27 16:43:22 发布

阅读量242

点赞数

文章标签： java 开发语言后端

本文链接：https://blog.youkuaiyun.com/qq_34784833/article/details/121099750

版权

获取内容的util类

package com.evergreen.util; 

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.TimeZone;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelCtrl 
{
    private String strSheet="0"; 
    public ExcelCtrl(){}
    private String getSheet(){
        return this.strSheet;
    }
    public void setSheet(String strSheet){
        this.strSheet=strSheet;
    }

    /**
     * 获得cell内容
     */
    private String getXSSFContent(Object o){
        String strReturn="";
        if(o==null)
            return strReturn;
        try{
            org.apache.poi.ss.usermodel.Cell cell=(org.apache.poi.ss.usermodel.Cell)o;
            switch (cell.getCellType()) {
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
                    strReturn="";
                    break;
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
                    strReturn = Boolean.toString(cell.getBooleanCellValue());
                    break;
                // 数值
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = null;
                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {// 日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        Date date = cell.getDateCellValue();
                        strReturn =sdf.format(date);
                    } else {
                        cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                        String temp = cell.getStringCellValue();
                        // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                        if (temp.indexOf(".") > -1) {
                            strReturn = String.valueOf(new Double(temp)).trim();
                        } else {
                            strReturn = temp.trim();
                        }
                    }
                    break;
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                    strReturn= cell.getStringCellValue().trim();
                    break;
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
                    strReturn = "";
                    break;
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
                    cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                    String temp = cell.getStringCellValue();
                    // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                    if (temp.indexOf(".") > -1) {
                        temp = String.valueOf(new Double(temp)).trim();
                        Double cny = Double.parseDouble(temp);//6.2041
                        DecimalFormat df = new DecimalFormat("0.00");
                        strReturn = df.format(cny);
                    } else {
                        strReturn = temp.trim();
                    }
                default:
                    strReturn = "";
                    break;
            }
            strReturn=strReturn.trim();
        }catch(Exception e){
            e.printStackTrace();
        }
        return strReturn;
    }
    /**
     * 获得内容
     */
    private ArrayList loadXlsxData(InputStream isFile){
        ArrayList resultList=new ArrayList();
        try{
            XSSFWorkbook wb = new XSSFWorkbook(isFile);
            String strBase=this.getSheet();
            if((strBase==null)||(strBase.trim().equals("")))
                strBase="0";
            XSSFSheet sheet = wb.getSheetAt(Integer.parseInt(strBase));
            int columnNum  = 0;
            if (sheet.getRow(0) != null) {
                columnNum  = sheet.getRow(0).getLastCellNum()- sheet.getRow(0).getFirstCellNum();
            }
            for (Row row : sheet) {
                ArrayList recordList=new ArrayList();
                for (int i = 0; i < columnNum; i++) {
                    org.apache.poi.ss.usermodel.Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
                    recordList.add(this.getXSSFContent(cell));
                }
                resultList.add(recordList);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return resultList;
    }
    /**
     * 获得cell内容
     */
    private String getContent(Cell tempCell){
        String strReturn="";
        if(tempCell==null)
            return strReturn;
        try{
            CellType ct=tempCell.getType();            
            if(ct.equals(CellType.LABEL)){
                strReturn=((LabelCell)tempCell).getString();
            }else if(ct.equals(CellType.NUMBER)){
                NumberCell nc = (NumberCell)tempCell;
                if(Math.round(nc.getValue())-nc.getValue()==0){
                    strReturn=String.valueOf((long)nc.getValue());
                }else{
                    strReturn=Double.toString(nc.getValue());
                }
            }else if(ct.equals(CellType.DATE)){
                DateCell dc=(DateCell)tempCell;
                Date jxlDate = dc.getDate();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                sdf.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
                strReturn=sdf.format(jxlDate);
            }else{
                strReturn=tempCell.getContents();
            }
            strReturn=strReturn.trim();
        }catch(Exception e){
            e.printStackTrace();
        }
        return strReturn;
    }
    /**
     * 获得数据
     */
    private ArrayList loadXlsData(InputStream isFile){
        ArrayList resultList=new ArrayList();
        try {
            Workbook rwb =Workbook.getWorkbook(isFile);
            String strBase=this.getSheet();
            if((strBase==null)||(strBase.trim().equals("")))
                strBase="0";
            Sheet rs = rwb.getSheet(Integer.parseInt(strBase));
            int intRows =rs.getRows();
            if(intRows>0){
                for (int i=0;i<intRows;i++){
                    ArrayList recordList=new ArrayList();
                    Cell[] cells = rs.getRow(i);
                    int iLength=cells.length;
                    for(int j=0;j<iLength;j++){
                        recordList.add(this.getContent(cells[j]));
                    }
                    resultList.add(recordList);
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return resultList;        
    }
    /**
    * 读取excel文件数据
    * @param isFile 文件输入流
    * @return lists 结果列表
    */
    public ArrayList readExcel(InputStream isFile,String strFileType){
        if((strFileType!=null)&&(strFileType.trim().equals(".xlsx"))){
            return this.loadXlsxData(isFile);
        }else{
            return this.loadXlsData(isFile);
        }
    }
}

创建表格的utli

package business.platform.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.List;
import java.util.Map;

/**
 * @program: HRMIS->ExcelUtil
 * @description: 生成Excel文件的工具类
 * @author: zhuqinglong
 * @create: 2021-08-02 14:44
 **/
public class ExcelUtil {
    /**
     * 创建excel文档，
     * @param list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    public static Workbook createWorkBook(List<Map<String, Object>> list, String []keys, String columnNames[]) {
        // 创建excel工作簿
        SXSSFWorkbook wb = new SXSSFWorkbook(100);//在内存中只保留100行记录,超过100就将之前的存储到磁盘里
        //HSSFWorkbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth(i, (int) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow(0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (int i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i);
            // 在row行上创建一个方格
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }
}

controller

 @RequestMapping(value = "/download", method = RequestMethod.GET)
    public void download(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String cs= request.getParameter("cs"); //前端传的参数
        List<Object> 0bject= 访问数据库得到你的数据
        List<Map<String,Object>> list=createExcelRecord(0bject);
        //列名
        String columnNames[]={"测试1","测试2", "测试3"};
        //map中的key
        String keys[] = {"cs1","cs2", "cs3"};
        Workbook workBook = ExcelUtil.createWorkBook(list, keys, columnNames);
        response.reset();
        response.setContentType("application/msexcel");
        response.setHeader( "Content-Disposition", "attachment;filename=" + new String( ("文件名.xlsx").getBytes("GB2312"), "8859_1" ));
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        OutputStream out = response.getOutputStream();
        workBook.write(out);
        out.flush();
        out.close();
    }


    private List<Map<String, Object>> createExcelRecord( List<Object> 0bjectlist) {
        List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("sheetName", "sheet1");
        listmap.add(map);
        Object 0bject2= null;
        for (int j = 0; j < 0bjectlist.size(); j++) {
            0bject2= 0bjectlist.get(j);
            Map<String, Object> mapValue = new HashMap<String, Object>();
            mapValue.put("cs1",0bject2.getCs1());
            .......
            .......

            listmap.add(mapValue);
        }
        return listmap;
    }

前端



<input type="button" class="btn btn-default btn-red-border" value="下载" onclick="downloadfile()">




    function downloadfile() {
        var url = "<%=request.getContextPath()%>/xxx/xxxx/xxxx/xxxxx.do";
        url+=("?cs="+cs);
        window.location.href=url;
    }