代码可以进一步优化,只是个例子:
public class TestExcel {
public static void main(String[] args){
try {
SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(10000);
Map<String, CellStyle> styles = createStyles(xssfWorkbook);
SXSSFSheet sheet = createWorksheet(xssfWorkbook, "test");
SXSSFRow row = sheet.createRow(0);
createCell(row, 0, styles.get(CellStyleKey.CELL_NORMAL), Cell.CELL_TYPE_BLANK).setCellValue("1");
createCell(row, 1, styles.get(CellStyleKey.CELL_NORMAL), Cell.CELL_TYPE_BLANK).setCellValue("1");
createCell(row, 2, styles.get(CellStyleKey.CELL_NORMAL), Cell.CELL_TYPE_BLANK).setCellValue("1");
FileOutputStream FileOutputStream = new FileOutputStream("D:/test/encrty.xlsx");
xssfWorkbook.write(FileOutputStream);
enctypt(FileOutputStream);
xssfWorkbook.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void enctypt(FileOutputStream FileOutputStream){
try {
POIFSFileSystem fs = new POIFSFileSystem();
EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
Encryptor enc = info.getEncryptor();
enc.confirmPassword("foobaa");
OPCPackage opc = OPCPackage.open(new File("D:/test/encrty.xlsx"), PackageAccess.READ_WRITE);
//OPCPackage opc = OPCPackage. .create(FileOutputStream);
OutputStream os = enc.getDataStream(fs);
opc.save(os);
opc.close();
FileOutputStream fos = new FileOutputStream("D:/test/encrty.xlsx");
fs.writeFilesystem(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static SXSSFCell createCell(SXSSFRow row, int cellindex, CellStyle style, int cellType) {
SXSSFCell cell = row.createCell(cellindex, cellType);
cell.setCellStyle(style);
return cell;
}
private static Map<String, CellStyle> createStyles(SXSSFWorkbook xssfWorkbook) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CreationHelper creationHelper = xssfWorkbook.getCreationHelper();
DataFormat df = creationHelper.createDataFormat();
CellStyle style = createBorderedStyle(xssfWorkbook);
Font headerFont = xssfWorkbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontName("Consolas");
headerFont.setFontHeightInPoints((short) 12);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);
styles.put(CellStyleKey.HEADER, style);
Font font = xssfWorkbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("Consolas");
font.setFontHeightInPoints((short) 12);
style = createBorderedStyle(xssfWorkbook);
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setFont(font);
styles.put(CellStyleKey.CELL_BOLD, style);
style = createBorderedStyle(xssfWorkbook);
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setWrapText(true);
styles.put(CellStyleKey.CELL_NORMAL, style);
style = createBorderedStyle(xssfWorkbook);
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setWrapText(true);
style.setDataFormat(df.getFormat("yyyy-MM-dd"));
styles.put(CellStyleKey.CELL_NORMAL_DATE, style);
style = createBorderedStyle(xssfWorkbook);
style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
styles.put(CellStyleKey.CELL_BG_BLUE, style);
return styles;
}
interface CellStyleKey {
public final static String HEADER = "header";
public final static String CELL_BOLD = "cell_b";
public final static String CELL_NORMAL = "cell_normal";
public final static String CELL_NORMAL_DATE = "cell_normal_date";
public final static String CELL_BG_BLUE = "cell_blue";
}
private static CellStyle createBorderedStyle(SXSSFWorkbook xssfWorkbook) {
CellStyle style = xssfWorkbook.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
interface CellStyleKey1 {
public final static String HEADER = "header";
public final static String CELL_BOLD = "cell_b";
public final static String CELL_NORMAL = "cell_normal";
public final static String CELL_NORMAL_DATE = "cell_normal_date";
public final static String CELL_BG_BLUE = "cell_blue";
}
private static SXSSFSheet createWorksheet(SXSSFWorkbook xssfWorkbook, String sheetName) {
if(StringUtils.isBlank(sheetName)){
sheetName = "" + System.currentTimeMillis();
}
SXSSFSheet sheet = xssfWorkbook.createSheet(sheetName);
sheet.setDisplayGridlines(false);
sheet.setPrintGridlines(false);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setDefaultColumnWidth(20);
return sheet;
}
}