1.获取excel对象
ClassPathResource classPathResource = new ClassPathResource("/templates/excel/aluminum_a.xlsx");
InputStream inputStream =classPathResource.getInputStream();
Workbook workbook = WorkbookFactory.create(inputStream );
2.获取sheet对象
Sheet sheet = workbook.getSheet("name");
Sheet sheet = workbook.getSheetAt(0);
Sheet sheet = workbook.createSheet();
Sheet sheet = workbook.createSheet("name");
3.获取row对象
Row row = sheet.getRow(0);
Row row = sheet.createRow(0);
private Row getRow(Sheet sheet,Integer index){
Row row = sheet.getRow(index);
if (row == null){
return sheet.createRow(index);
}
return row;
}
4.获取cell对象
Cell cell = row.getCell(0);
Cell cell = row.createCell(0);
private Cell getCell(Row row,Integer index){
Cell cell = row.getCell(index);
if (cell == null){
return row.createCell(index);
}
return cell;return cell;
}
5.合并单元格
CellRangeAddress rangeAddress = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);
sheet.addMergedRegion(rangeAddress);
6.取消合并单元格
public static void removeMergedRegion(Sheet sheet, int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
int index = 0;
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow)
{
if(column >= firstColumn && column <= lastColumn)
{
index = i;
}
}
}
sheet.removeMergedRegion(index);
}
7.行高和单元格样式
Sheet sheet=workbook.getSheetAt(0);
Row row=sheet.getRow(0);
row.setHeight((short) (43 * 20));
Cell cell=row.getCell(0);
CellStyle style = workbook.createCellStyle();
style.cloneStyleFrom(cell.getCellStyle());
style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setWrapText(true);
Font font= row.getSheet().getWorkbook().createFont();
font.setFontName("宋体");
font.setFontHeight((short) 18);
font.setColor(IndexedColors.BLUE_GREY.getIndex());
font.setBold(true);
font.setItalic(true);
font.setUnderline(Font.U_SINGLE);
font.setTypeOffset(Font.SS_SUPER);
font.setStrikeout(true);
style.setFont(font);
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLUE_GREY.getIndex());
style.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex());
style.setBottomBorderColor(IndexedColors.BLUE_GREY.getIndex());
style.setLeftBorderColor(IndexedColors.BLUE_GREY.getIndex());
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
sheet.setDefaultColumnWidth(0);
sheet.setColumnWidth(0,10);
row.setHeight((short)10);
sheet.createFreezePane(1, 1);
8.设置字段里面某个字符串的样式
XSSFRichTextString richTextString = (XSSFRichTextString)sheet.getWorkbook()
.getCreationHelper()
.createRichTextString(title);
Font font = sheet.getWorkbook().createFont();
font.setColor(IndexedColors.RED.getIndex());
font.setFontHeightInPoints((short)18);
font.setFontName("宋体");
richTextString.applyFont(4,6,font);
Font font1 = sheet.getWorkbook().createFont();
font1.setFontName("宋体");
font1.setFontHeightInPoints((short)18);
richTextString.applyFont(6,title.length(),font1);
cellStyle.setFont(font1);
cell.setCellStyle(cellStyle);
cell.setCellValue(richTextString);