Java Excel 下载模板以及导入数据，下载模板带单元格格式（下拉以及时间）

最新推荐文章于 2024-03-29 18:18:32 发布

时不我待、

最新推荐文章于 2024-03-29 18:18:32 发布

阅读量1.3k

点赞数 1

分类专栏： Java 文章标签： Excel 导入导出单元格格式

本文链接：https://blog.csdn.net/wxn069/article/details/106381552

版权

Java 专栏收录该内容

39 篇文章 1 订阅

订阅专栏

import com.alibaba.excel.support.ExcelTypeEnum;
import com.inco.entity.bjd.JsxxEntity;
import com.inco.project.Zcd.controller.ExcelController;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("/excel")
public class ExcelClass {

    /**
     * 创建Excel 导出模板
     */
    @RequestMapping("/DownTemplate")
    public void DownTemplate(HttpServletResponse response) throws IOException {
        //创建workbook工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet = (XSSFSheet) workbook.createSheet();
        response.setCharacterEncoding("UTF-8");
//        sheet.setColumnWidth(1,100*50);
        sheet.setDefaultColumnWidth(300*100);//默认宽度

        // 设置为文本格式，防止身份证号变成科学计数法
        DataFormat format = workbook.createDataFormat();
        // 创建单元格样式 文本
        CellStyle Textstyle= workbook.createCellStyle();
        Textstyle.setDataFormat(format.getFormat("@"));
        // 创建单元格样式 时间格式
        CellStyle Datestyle = workbook.createCellStyle();
        Datestyle.setDataFormat(format.getFormat("yyyy/MM/dd"));

        //对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
        sheet.setDefaultColumnStyle(0, Textstyle);
        sheet.setDefaultColumnStyle(1, Datestyle);


        XSSFCell cell = null;
        //第一行
        XSSFRow row0 = sheet.createRow((short) 0);
        row0.setHeight((short)800);//高度
        XSSFCellStyle style = workbook.createCellStyle();
        //这种写法适用于poi 版本高的时候
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//        //这种写法适用于poi 版本低的时候
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平对齐的样式为居中对齐;
//        style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);//设置垂直对齐的样式为居中对齐;
        //设置字体
        XSSFFont font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 18);// 设置字体大小
        style.setFont(font);
        //第一行 第一列
        cell = row0.createCell(0);
        cell.setCellValue("第一行内容");//内容
        cell.setCellStyle(style);//样式
        //参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
        sheet.addMergedRegion(new CellRangeAddress(0, (short) (0), 0, (short) (1)));//合并

        //设置样式
        XSSFCellStyle style1 = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //第二行
        XSSFRow row1 = sheet.createRow((short) 1);
        row1.setHeight((short)400);

        cell = row1.createCell(0);
        cell.setCellValue("第二行第一列");
        cell.setCellStyle(style1);
        //给当前列添加下拉选项 如果不需要的话可以忽略这个
        String a[] = {"是","否"};
        addDropDownList(workbook,sheet,a,2,0);

        cell = row1.createCell(1);
        cell.setCellValue("第二行第二列");
        cell.setCellStyle(style1);

        ServletOutputStream outStream = response.getOutputStream();
        sheet.getPrintSetup().setLandscape(true);//设置为横向打印
        String fileName ="模板"+ ExcelTypeEnum.XLSX.getValue();
        response.setHeader("Content-disposition","attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));
        response.setContentType("application/octet-stream;charset=UTF-8");
        workbook.write(outStream);
        outStream.close();
    }


    /**
     * 导入 读取Excel返回一个list
     * @param file
     * @return
     */
    @PostMapping("/importa")
    public Object importa(MultipartFile file){
        String fileName = file.getOriginalFilename();// 获得上传文件的文件名
        String eName = fileName.substring(fileName.lastIndexOf(".")+1);// 获取文件扩展名
        InputStream inputStream = null;
        //格式化时间
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        Workbook workbook = getWorkbook(inputStream, eName);//根据excel文件格式获知excel版本信息
        Sheet sheet = workbook.getSheetAt(0);// 获取工作薄第一张表
        Row row = null;
        int rowNum = sheet.getPhysicalNumberOfRows();// 获得有效行数
        List<Object> objectList = new ArrayList<>();// 定义导出文件中数据集合
        for (int i = 2; i < rowNum; i++) {//循环行 第一行总标题 第二行标头 所以从第三行开始
            List<String> d = new ArrayList<String>();
            row = sheet.getRow(i);
            for (int j = 0; j < 1; j++) {//循环列  这个有多少列就写多少列就行
                d.add(getCellValue(row.getCell(j)));
            }
            objectList.add(d);
        }
        return objectList;
    }





    /**
     * 通过隐藏的工作表来给要导出的工作表中某列添加下拉选项以及格式
     * @param workbook 工作簿
     * @param sheet 工作表
     * @param menuItems 下拉数组
     * @param firstRow 第一个目标单元格所在的行号(从0开始) 假如是1就是从第二行开始显示下拉
     * @param lastRow  最后一个目标单元格所在的行(从0开始)
     * @param column 第几列(从0开始) 在第几列显示下拉框
     */
    private static void  addDropDownList(XSSFWorkbook workbook,XSSFSheet sheet,String[] menuItems,int firstRow, int column){
        //excel中的"名称"，用于标记隐藏sheet中的用作菜单下拉项的所有单元格
        //必须以字母开头，最长为31位 名字不能重复
        String hiddenSheetName = "hidden_"+ UUID.randomUUID().toString().replace("-", "").substring(1, 20);
        //用于存储 下拉菜单数据
        XSSFSheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        //存储下拉菜单项的sheet页不显示
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = 0, length = menuItems.length; i < length; i++) {
            row = hiddenSheet.createRow(i);
//            cell = row.createCell(column);//当用这个的时候就用one，two给注释掉，这个是隐藏表的数据列必须和添加下拉菜单的列序号相同，否则不能显示下拉菜单
            cell = row.createCell(0);//当用这个的时候就用two，one给注释掉
            cell.setCellValue(menuItems[i]);
        }
        //one begin
//            XSSFName namedCell = workbook.createName();//创建"名称"标签，用于链接
//            namedCell.setNameName(hiddenSheetName);
//            namedCell.setRefersToFormula(hiddenSheetName + "!A$1:A$" + menuItems.length);
//            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
//            DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(hiddenSheetName);
//            CellRangeAddressList addressList = new CellRangeAddressList(1, 65534, column, column);
//            //添加菜单(将单元格与"名称"建立关联)
//            XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
//            sheet.addValidationData(validation);
        //one end

        //two begin
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(hiddenSheet);
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(hiddenSheetName + "!A$1:A$" + menuItems.length);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, 65534, column, column);
        //添加菜单(将单元格与"名称"建立关联)
        XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
        // 输入非法数据时，弹窗警告框 输入无效值时是否显示错误框
        validation.setShowErrorBox(true);
        // 验证输入数据是否真确
        validation.setSuppressDropDownArrow(true);
        sheet.addValidationData(validation);
        //two end
    }





    /*
     * 根据excel文件格式获知excel版本信息
     */
    public Workbook getWorkbook(InputStream fs,String str){
        Workbook book = null;
        try{
            if ("xls".equals(str)) {
                // 2003
                book = new HSSFWorkbook(fs);
            } else {
                // 2007
                book = new XSSFWorkbook(fs);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return book;
    }

    /**
     * 验证单元格的格式
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell){
        String value = "";
        if (cell!=null){
            //判断类型
            switch (cell.getCellTypeEnum()){
                case NUMERIC:
                    value = cell.getNumericCellValue()+"";
                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                        Date date = cell.getDateCellValue();
                        if(date!=null){
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        }else {
                            value="";
                        }
                    }else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case STRING://string
                    value = cell.getStringCellValue()+"";
                    break;
                case BOOLEAN://boolean
                    value = cell.getBooleanCellValue()+"";
                    break;
                case FORMULA://公式
                    value = cell.getCellFormula()+"";
                    break;
                case BLANK://空
                    value = "";
                    break;
                case ERROR://故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }
}

补充一个导出超链接的问题：

//HSSFWorkbook：这个类包含了读取或写入.xls格式文件的方法。能处理的版本包括 97-2003.
HSSFCell cell = row.createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);//CELL_TYPE_FORMULA
cell.setCellFormula("HYPERLINK(\"" + link + "\",\""+ cellValue +"\")");

//XSSFWorkbook：这个类包含了读取或写入.xlsx或.xls文件格式的方法，能够处理2007及以后版本的文档。
XSSFCell cell = row.createCell(0);
cell.setCellFormula("HYPERLINK(\"" + codeLink + "\",\""+ code +"\")");