Java创建Excel且设置一列样式为文本类型

动态创建Excel表格

创建Excel实体类

package com.homeinns.microsrvpmsadminservice.utils.excelUtils;

import com.homeinns.microsrvpmscommon.utils.excelUtils.DateType;
import com.homeinns.microsrvpmscommon.utils.excelUtils.ExcelField;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil_User {
// 生成excel，list导出的数据，list里的实体class，sumData合计数据
public static XSSFWorkbook createExcel(List list, Class cls, Q sumData)
throws IllegalArgumentException, IllegalAccessException {
XSSFWorkbook wb = new XSSFWorkbook();
Field[] fields = cls.getDeclaredFields();
ArrayList headList = new ArrayList();

    // 添加合计数据
    if (sumData != null) {
        list.add(sumData);
    }

    for (Field f : fields) {
        ExcelField field = f.getAnnotation(ExcelField.class);
        if (field != null) {
            headList.add(field.title());
        }
    }

    XSSFCellStyle style = getCellStyle(wb);
   // XSSFCellStyle styles = getCellStyles(wb);
    XSSFSheet sheet = wb.createSheet();

    XSSFCellStyle style1=wb.createCellStyle();
    XSSFDataFormat format=wb.createDataFormat();
    style1.setDataFormat(format.getFormat("@"));

    // 设置Excel表的第一行即表头
    XSSFRow row = sheet.createRow(0);

    sheet.createFreezePane(0, 1, 0, 1);
    row.setHeight((short) 500);
    for (int i = 0; i < headList.size(); i++) {
        XSSFCell headCell = row.createCell(i);
        headCell.setCellType(Cell.CELL_TYPE_STRING);
        headCell.setCellStyle(style);// 设置表头样式
        headCell.setCellValue(String.valueOf(headList.get(i)));
        if (headList.get(i).contains("备注") ||
                headList.get(i).contains("中介订单号") ||
                headList.get(i).contains("摘要") ||
                headList.get(i).contains("【")) {
            sheet.setColumnWidth(i, 30 * 256);
            continue;
        }
        if (headList.get(i).contains("姓名") ||
                headList.get(i).contains("手机") ||
                headList.get(i).contains("证件号码")) {
            sheet.setColumnWidth(i, 28 * 256);
            sheet.setDefaultColumnStyle(i,style1);//将此列样式设为文本
            continue;
        }
        if (headList.get(i).contains("状态") ||
                headList.get(i).contains("Tag") ||
                headList.get(i).contains("班别") ||
                headList.get(i).contains("班次") ||
                headList.get(i).contains("人数") ||
                headList.get(i).contains("国籍") ||
                headList.get(i).contains("保密") ||
                headList.get(i).contains("客密") ||
                headList.get(i).contains("房号")) {
            sheet.setColumnWidth(i, 8 * 256);
            continue;
        }
        if (headList.get(i).contains("日期") ||
                headList.get(i).contains("时间") ||
                headList.get(i).contains("有效期") ||
                headList.get(i).contains("到达") ||
                headList.get(i).contains("单位") ||
                headList.get(i).contains("生日") ||
                headList.get(i).contains("离开")) {
            sheet.setColumnWidth(i, 20 * 256);
            continue;
        }
        if (headList.get(i).contains("部门")) {
            sheet.setColumnWidth(i, 20 * 256);
            continue;
        }
        sheet.autoSizeColumn(i);// 设置单元格自适应
        sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 22 / 10);
    }


    for (int i = 0; i < list.size(); i++) {
        XSSFRow rowdata = sheet.createRow(i + 1);// 创建数据行
        //rowdata.setHeight((short) 400);
        Q q = list.get(i);
        Field[] ff = q.getClass().getDeclaredFields();
        int j = 0;
        for (Field f : ff) {
            ExcelField field = f.getAnnotation(ExcelField.class);
            if (field == null) {
                continue;
            }
            f.setAccessible(true);
            Object obj = f.get(q);

            XSSFCell cell = rowdata.createCell(j);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(style1);


            // 当数字时
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            }
            // 当为字符串时
            else if (obj instanceof String) {
                cell.setCellValue((String) obj);
            }
            // 当为布尔时
            else if (obj instanceof Boolean) {
                cell.setCellValue((Boolean) obj);
            }
            // 当为时间时
            else if (obj instanceof Date) {

                if (f.getAnnotation(DateType.class) != null && f.getAnnotation(DateType.class).type().equals("datetime")) {
                    String stringDate = getStringDateTime((Date) obj);
                    cell.setCellValue(stringDate);
                }
                if (f.getAnnotation(DateType.class) != null && f.getAnnotation(DateType.class).type().equals("date")) {
                    String stringDate = getStringDate((Date) obj);
                    cell.setCellValue(stringDate);
                }
            }
            j++;
        }
    }


    if (sumData != null) {
        int rowIndex = list.size();
        XSSFRow sumRow = sheet.getRow(rowIndex);
        XSSFCell sumCell = sumRow.getCell(0);
        sumCell.setCellStyle(style);
        sumCell.setCellValue("合计");
    }
    return wb;
}

// 导出
public static void writeExcel(HttpServletResponse response, String fileName, XSSFWorkbook wb) throws IOException {
    response.setContentType("application/x-download");
    response.setCharacterEncoding("UTF-8");
    // 暴露Content-Disposition给前段获取
    response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    OutputStream ouputStream = null;
    try {
        ouputStream = response.getOutputStream();
        wb.write(ouputStream);
    } finally {
        ouputStream.close();
    }
}

// 表头样式
public static XSSFCellStyle getCellStyle(XSSFWorkbook wb) {
    XSSFCellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setFontName("黑体");
    font.setColor(HSSFColor.WHITE.index);
    font.setFontHeightInPoints((short) 12);// 设置字体大小
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
    style.setFillForegroundColor(HSSFColor.DARK_TEAL.index);// 设置背景色
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);// 让单元格居中
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
    style.setWrapText(true);// 设置自动换行
    style.setFont(font);
    return style;
}

// 数据行样式
public static XSSFCellStyle getCellStyles(XSSFWorkbook wb) {
    XSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);// 让单元格居中
    //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
    style.setWrapText(true);// 设置自动换行
    return style;
}

private static String getStringDate(Date date) {
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    String dateString = formatter.format(date);
    return dateString;
}

private static String getStringDateTime(Date date) {
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String dateString = formatter.format(date);
    return dateString;
}

}

设置身份证列为文本格式

Control层

@ApiOperation(value = “模板导出”)
@PostMapping(“/excelmodel”)
public void UserToExcelModel(HttpServletResponse response){
List list = new ArrayList<>();
list.add(new UserExcel());
try {
XSSFWorkbook excel = ExcelUtil_User.createExcel(list, UserExcel.class, null);
String filename = getExcelName(System.currentTimeMillis() + “”);
ExcelUtil_User.writeExcel(response, filename, excel);
} catch (Exception e) {
e.printStackTrace();
}
}

getExcelName类

// 转化为excel名称
private String getExcelName(String filename) throws UnsupportedEncodingException {
String excelName = StringUtils.join(filename, “.xlsx”);
return URLEncoder.encode(excelName, “UTF-8”);
}