POI 操作Excel

pom依赖如下：

<dependency>
    <groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.0</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.0</version>
</dependency>

1. 创建，读取，输出表格

// 创建xls后缀文件表格
Workbook wb = new HSSFWorkbook();

// 创建xlsx后缀文件表格
Workbook wb = new XSSFWorkbook();

// 读取表格
try (InputStream inp = new FileInputStream("workbook.xlsx")) {

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
}

 // 输出表格
try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
	wb.write(fileOut);
}
wb.close();

2. 创建sheet

sheet名字不能超过31个字符，而且不同包含一些特殊字符

Sheet sheet = wb.createSheet("new sheet");

3. 创建单元格

Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(1);

4. 单元格显示时间

//设置单元格时间显示格式
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm"));

cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
// 不设置格式， 最终在excel显示的是1900年0时0分0秒到现在的天数，是一个小数
cell = row.createCell(3);
cell.setCellValue(new Date());

5. 不同类型的单元格

POI没有日期类型单元格，由CellType这个枚举类可以看出，只有数字，字符串，公式，空白，布尔，error六种

row = sheet.createRow(1);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue("a string");
row.createCell(2).setCellFormula("A1+A1");
        
Cell cellBlank = row.createCell(3);
cellBlank.setCellValue("1");
cellBlank.setBlank();
        
row.createCell(4).setCellValue(true);
row.createCell(5).setCellErrorValue(FormulaError.NULL.getCode());

6. 遍历单元格

int rowStart = sheet.getFirstRowNum();
int rowEnd =  sheet.getLastRowNum();
        
for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

	Row r = sheet.getRow(rowNum);
	if (r == null) {
		// 整行是空的
		continue;
	}
	int lastColumn = r.getLastCellNum();
	for (int cn = 0; cn < lastColumn; cn++) {
		Cell c = r.getCell(cn);
        if (c == null) {
			// 单元格是空的
        	System.out.println("empty");
        } else {
                    // 单元格不是空的
            switch (c.getCellType()) {
                case STRING: System.out.println("string:" + c.getStringCellValue());break;
                case NUMERIC:System.out.println("num:" + c.getNumericCellValue());break;
                case BOOLEAN:System.out.println("boolean:" + c.getBooleanCellValue());break;
                case FORMULA: System.out.println("formula:" + c.getCellFormula() );break;
                case BLANK:System.out.println("blank:" + c );break;
                case ERROR:System.out.println("error:" + c.getErrorCellValue());break;
                default:System.out.println("other");
            }
        }
    }
}

7. 另一种获取单元格内容方式

int rowStart = sheet.getFirstRowNum();
int rowEnd =  sheet.getLastRowNum();
DataFormatter formatter = new DataFormatter();
for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

    Row r = sheet.getRow(rowNum);
    if (r == null) {
          // 整行是空的
          continue;
    }
	int lastColumn = r.getLastCellNum();
	for (int cn = 0; cn < lastColumn; cn++) {
        Cell c = r.getCell(cn);
        CellReference cellRef = new CellReference(r.getRowNum(), c.getColumnIndex());

        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");
        String text = formatter.formatCellValue(c);
        System.out.println(text);
     }
}

8. 提取文本, 只支持xls格式excel

ExcelExtractor extractor = new ExcelExtractor(wb);
extractor.setFormulasNotResults(true);
extractor.setIncludeSheetNames(false);
System.out.println(extractor.getText());

9. 单元格对齐

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(2);
row.setHeightInPoints(30);
// HorizontalAlignment水平对齐   VerticalAlignment垂直对齐
createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
// CENTER_SELECTION 选择范围内中央对齐
createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
// FILL 会将内容复制多份填充单元格
createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
// GENERAL 标准，就是初始的单元格
createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
// JUSTIFY 调节对齐，单元格内容自动换行
createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);

private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {

	Cell cell = row.createCell(column);
	cell.setCellValue("Align It");
	CellStyle cellStyle = wb.createCellStyle();
	cellStyle.setAlignment(halign);
	cellStyle.setVerticalAlignment(valign);
	cell.setCellStyle(cellStyle);
}

10. 单元格边框

Workbook wb = new XSSFWorkbook();

Sheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow(1);
Cell cell = row.createCell(1);
cell.setCellValue(4);

CellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(BorderStyle.MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());

cell.setCellStyle(style);

11. 填充单元格

CellStyle style = wb.createCellStyle();
// 设置填充背景颜色
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
// 设置填充模式
style.setFillPattern(FillPatternType.BIG_SPOTS);
Cell cell = row.createCell(1);
cell.setCellValue("X");
cell.setCellStyle(style);

style = wb.createCellStyle();
// 设置填充前景颜色
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell = row.createCell(2);
cell.setCellValue("X");
cell.setCellStyle(style);

12. 合并单元格

Cell cell = row.createCell(1);
cell.setCellValue("This is a test of merging");
sheet.addMergedRegion(new CellRangeAddress(
                1, //first row (0-based)
                1, //last row  (0-based)
                1, //first column (0-based)
                2  //last column  (0-based)
 ));

CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");
sheet.addMergedRegion( region );

13. 设置字体

Font font = wb.createFont();

font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);

CellStyle style = wb.createCellStyle();
style.setFont(font);

Cell cell = row.createCell(1);
cell.setCellValue("测试字体，This is a test of fonts");
cell.setCellStyle(style);

14. 单元格内换行

Cell cell = row.createCell(2);
cell.setCellValue("Use \n with word wrap on to create a new line");
// 要启用换行符，需要使用wrap = true设置单元格样式
CellStyle cs = wb.createCellStyle();
cs.setWrapText(true);

cell.setCellStyle(cs);
// 增加行高以容纳两行文本
row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
// 调整列宽以适合内容
sheet.autoSizeColumn(2);

15. 单元格数据格式化

Row row;
CellStyle style;
Cell cell;
DataFormat format = wb.createDataFormat();
int rowNum = 0;
int colNum = 0;
        
row = sheet.createRow(rowNum++);  
cell = row.createCell(colNum);
cell.setCellValue(11111.25);
style = wb.createCellStyle();
// 这个格式字符串可以看BuiltinFormats类的字符串数组_formats
style.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(style);
        
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(11111.25);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
        
cell.setCellStyle(style);

16. sheet移动行

// 第5到第10(0为基准，实际就是第6到第11行), 往下移动5行, 负数就是往上移动
sheet.shiftRows(5, 10, 5);

17. 将sheet设为选中

sheet.setSelected(true);

18. 设置sheet的缩放放大倍数

sheet.setZoom(75);

19. 创建拆分和冻结窗格

// 冻结窗格
sheet.createFreezePane( 0, 1, 0, 1 );
// 拆分
sheet.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );

20. 插入图片

InputStream is = new FileInputStream("image1.jpg");
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
is.close();

CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(0);
anchor.setRow1(0);

Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize(1, 1);   

21. 单元格评论

CreationHelper factory = wb.getCreationHelper();

Row row   = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("");

Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex()+1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum()+3);
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString("你好");
comment.setString(str);
comment.setAuthor("someone");
cell.setCellComment(comment);

22. 设置下拉框

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");

DataValidationHelper helper = new XSSFDataValidationHelper(sheet);

CellRangeAddressList addressList = new CellRangeAddressList(0, 1, 0, 0);
//设置下拉框数据
String [] list = {"张三", "李四"};
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
	dataValidation.setSuppressDropDownArrow(true);
	dataValidation.setShowErrorBox(true);
} else {
	dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);

23. 开启自动筛选功能

sheet.setAutoFilter(CellRangeAddress.valueOf("A1:F20"));

24. 设置单元格属性

Map<String, Object> properties = new HashMap<>();
properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM);
properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM);
properties.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM);
properties.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM);
properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex());
properties.put(CellUtil.BOTTOM_BORDER_COLOR, IndexedColors.RED.getIndex());
properties.put(CellUtil.LEFT_BORDER_COLOR, IndexedColors.RED.getIndex());
properties.put(CellUtil.RIGHT_BORDER_COLOR, IndexedColors.RED.getIndex());

Row row = sheet.createRow(1);
Cell cell = row.createCell(1);
CellUtil.setCellStyleProperties(cell, properties);

for (int ix = 3; ix <= 5; ix++) {
	row = sheet.createRow(ix);
	for (int iy = 3; iy <= 5; iy++) {
		cell = row.createCell(iy);
		CellUtil.setCellStyleProperties(cell, properties);
	}
}

25. 绘制边框

PropertyTemplate pt = new PropertyTemplate();
pt.drawBorders(new CellRangeAddress(1, 3, 1, 3),
                BorderStyle.MEDIUM, BorderExtent.ALL);
pt.drawBorders(new CellRangeAddress(5, 7, 1, 3),
                BorderStyle.MEDIUM, BorderExtent.OUTSIDE);
pt.drawBorders(new CellRangeAddress(5, 7, 1, 3), BorderStyle.THIN,
                BorderExtent.INSIDE);
pt.drawBorders(new CellRangeAddress(9, 11, 1, 3),
                BorderStyle.MEDIUM, IndexedColors.RED.getIndex(),
                BorderExtent.OUTSIDE);
pt.drawBorders(new CellRangeAddress(9, 11, 1, 3),
                BorderStyle.MEDIUM, IndexedColors.BLUE.getIndex(),
                BorderExtent.INSIDE_VERTICAL);
pt.drawBorders(new CellRangeAddress(9, 11, 1, 3),
                BorderStyle.MEDIUM, IndexedColors.GREEN.getIndex(),
                BorderExtent.INSIDE_HORIZONTAL);
pt.drawBorders(new CellRangeAddress(10, 10, 2, 2),
                BorderStyle.NONE,
                BorderExtent.ALL);
pt.applyBorders(sheet);

参考

https://poi.apache.org/components/spreadsheet/quick-guide.html

场景

导出的数值单元格格式是文本。 客户每次都要手动转为数值，往往一个表格就是好多万数据，转换起来等半天。

解决方案

听说@Excel 设置type=10即可。 实测无效。

设置type=10对不对

这样设置是对的。 但是为什么没有效果呢?
仔细跟代码，发现type=10，会设置单元格的type为 Cell.CELL_TYPE_NUMERIC 。 单元格的值属于CellValue ，和CelType都是属于Cell 。
但是单元格属性 属于 CellStyle。
所以虽然值的输出符合规范了，但是单元格显示还是为文本。

导出用到的主要元素和依赖关系

使用easypoi 导出excel只用简单的一个方法。
ExcelExportUtil.exportExcel(params, StatisticEntity.class, list);
主要元素列表如下：

List<StatisticEntity> list = new ArrayList<StatisticEntity>(); // TODO list中添加数据根据情况自己写
ExportParams params = new ExportParams("2412312", "测试", ExcelType.XSSF);
params.setStyle(ExcelExportStatisticStyler.class);
Workbook workbook = ExcelExportUtil.exportExcel(params, StatisticEntity.class, list);

打造属于自己的styler

好的，既然ExportParams 可以setStyle，那么我们写个类，继承ExcelExportStylerDefaultImpl 重写 getStyles方法不就成了么。

错误的写法

思路，依照最小改动原则，先获取已有的style，然后添加type=10的处理逻辑。代码：

@Override
public CellStyle getStyles2(boolean noneStyler, ExcelExportEntity entity) {
    CellStyle styles = super.getStyles(noneStyler, entity); // 获取style
    // type=10 的处理
    if (entity != null && 10==entity.getType()) {
        styles.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00"));
        return styles;
    }
    return styles;
}

测了下发现报错， 是因为子类重写getStyles()方法，super.getStyles()调的就是子类的getStyles()方法。 这不无限循环了么。

重新写逻辑

代码：

@Override
public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity) {
    if (entity != null
        && 10==entity.getType()) {
        return numberCellStyle;
    }
    return super.getStyles(noneStyler, entity);
}

实测成功。
导出的excel单元格格式为 自定义 0.00 。这还不是客户要求的数值格式。但是只能做到这了。
至少我目前没找到如何设置为数值格式。

ExcelExportStatisticStyler 类完整的代码

public class ExcelExportStatisticStyler extends ExcelExportStylerDefaultImpl {

    private CellStyle numberCellStyle;

    public ExcelExportStatisticStyler(Workbook workbook) {
        super(workbook);
        createNumberCellStyler();
    }

    private void createNumberCellStyler() {
        numberCellStyle = workbook.createCellStyle();
        numberCellStyle.setAlignment(HorizontalAlignment.CENTER);
        numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        numberCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00"));
        numberCellStyle.setWrapText(true);
    }

    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity) {
        if (entity != null
            && 10==entity.getType()) {
            return numberCellStyle;
        }
        return super.getStyles(noneStyler, entity);
    }
}

setDataFormat有2种方式入参形式

1、字符串格式(推荐，可读性更强)
2、_formats 数组的下标

两种方式，本质上没区别， 传入string也是根据字面值，找_formats对应的下标。
见BuiltinFormats源码 (强调 - 自定义格式一定要在_formats 数组内，否则无效)：

public static int getBuiltinFormat(String pFmt) {
	String fmt = "TEXT".equalsIgnoreCase(pFmt) ? "@" : pFmt;

	int i = -1;
	for (String f : _formats) {
	    i++;
	    if (f.equals(fmt)) {
	        return i; 
	    }
	}
	
	return -1;
}

两种设置的例子：

// 使用字符串定义格式
cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00"));
// BuiltinFormats._formats 数组中的下标
cellStyle.setDataFormat((short) 1); 

解决总结

步骤：
@Excel 中添加 type=10
写个类继承ExcelExportStylerDefaultImpl ，重写getStyles()方法
导出前ExportParams。

既然重写了getStyles()，那么其实type=10并不是唯一的办法。
官网就是根据name中是否包含int，double等类型来判断的，当然@Excel中的name要记得配合。(感觉type=10比改name更方便，更解耦)

setAlignment(HorizontalAlignment.CENTER) 报错

版本的问题。 报错代码如下：

numberCellStyle.setAlignment(HorizontalAlignment.CENTER);
numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

调整为如下代码即可：

numberCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
numberCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

debug跟代码一行一行看

ExcelExportBase类中的createCells方法中，如果类型为double，则进入如下逻辑：

else if (entity.getType() == BaseEntityTypeConstants.DoubleType) {
}

ExcelExportBase类中的createDoubleCell方法(type=10时设置了cellType)：

public void createDoubleCell(Row row, int index, String text, CellStyle style,ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    if (text != null && text.length() > 0) {
        cell.setCellValue(Double.parseDouble(text));
    } else {
        cell.setCellValue(-1);
    }
    cell.setCellType(Cell.CELL_TYPE_NUMERIC); // 这里是设置了celltype为0，表示数字格式
    if (style != null) {  // 这里设置style
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

是不是type不影响页面excel的文本格式，而是由style控制的呢。

easypoi支持的自定义格式列表

BuiltinFormats类的_formats列表里的自定义格式才有效，否则就会使用文本格式。

private final static String[] _formats = {
        "General",
        "0",
        "0.00",
        "#,##0",
        "#,##0.00",
        "\"$\"#,##0_);(\"$\"#,##0)",
        "\"$\"#,##0_);[Red](\"$\"#,##0)",
        "\"$\"#,##0.00_);(\"$\"#,##0.00)",
        "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)",
        "0%",
        "0.00%",
        "0.00E+00",
        "# ?/?",
        "# ??/??",
        "m/d/yy",
        "d-mmm-yy",
        "d-mmm",
        "mmm-yy",
        "h:mm AM/PM",
        "h:mm:ss AM/PM",
        "h:mm",
        "h:mm:ss",
        "m/d/yy h:mm",

        // 0x17 - 0x24 reserved for international and undocumented
        // TODO - one junit relies on these values which seems incorrect
        "reserved-0x17",
        "reserved-0x18",
        "reserved-0x19",
        "reserved-0x1A",
        "reserved-0x1B",
        "reserved-0x1C",
        "reserved-0x1D",
        "reserved-0x1E",
        "reserved-0x1F",
        "reserved-0x20",
        "reserved-0x21",
        "reserved-0x22",
        "reserved-0x23",
        "reserved-0x24",
        
        "#,##0_);(#,##0)",
        "#,##0_);[Red](#,##0)",
        "#,##0.00_);(#,##0.00)",
        "#,##0.00_);[Red](#,##0.00)",
		"_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)",
        "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)",
        "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)",
        "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)",
        "mm:ss",
        "[h]:mm:ss",
        "mm:ss.0",
        "##0.0E+0",
        "@"
	};

知识点

参照org.apache.poi.ss.usermodel.BuiltinFormats的说明

Open Declaration org.apache.poi.ss.usermodel.BuiltinFormats

Utility to identify built-in formats. The following is a list of the formats as returned by this class.

0, "General"
1, "0"
2, "0.00"
3, "#,##0"
4, "#,##0.00"
5, "$#,##0_);($#,##0)"
6, "$#,##0_);[Red]($#,##0)"
7, "$#,##0.00);($#,##0.00)"
8, "$#,##0.00_);[Red]($#,##0.00)"
9, "0%"
0xa, "0.00%"
0xb, "0.00E+00"
0xc, "# ?/?"
0xd, "# ??/??"
0xe, "m/d/yy"
0xf, "d-mmm-yy"
0x10, "d-mmm"
0x11, "mmm-yy"
0x12, "h:mm AM/PM"
0x13, "h:mm:ss AM/PM"
0x14, "h:mm"
0x15, "h:mm:ss"
0x16, "m/d/yy h:mm"
// 0x17 - 0x24 reserved for international and undocumented 0x25, "#,##0_);(#,##0)"
0x26, "#,##0_);[Red](#,##0)"
0x27, "#,##0.00_);(#,##0.00)"
0x28, "#,##0.00_);[Red](#,##0.00)"
0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)"
0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"
0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
0x2d, "mm:ss"
0x2e, "[h]:mm:ss"
0x2f, "mm:ss.0"
0x30, "##0.0E+0"
0x31, "@" - This is text format.
0x31 "text" - Alias for "@"

 实现

//设置单元格格式为文本格式
CellStyle textStyle = sxssfWorkbook.createCellStyle();
DataFormat format = sxssfWorkbook.createDataFormat();
textStyle.setDataFormat(format.getFormat("@"));

cell.setCellStyle(textStyle);