I am using Apache POI to generate an Excel file. I need to delete all of the borders in my worksheet. How can I accomplish this using Apache PIO 3.11 and Microsoft Excel 2007?
Here is the code I have so far:
package models;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.util.List;
public class Excel {
public static void writeDocument() {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("sheet");
//first font
Font font1 = workbook.createFont();
font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
//first style
CellStyle style1 = workbook.createCellStyle();
style1.setBorderLeft(CellStyle.BORDER_NONE);
style1.setBorderRight(CellStyle.BORDER_NONE);
style1.setBorderBottom(CellStyle.BORDER_NONE);
style1.setBorderTop(CellStyle.BORDER_NONE);
//second style
CellStyle style2 = workbook.createCellStyle();
style2.setFont(font1);
style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style2.setAlignment(CellStyle.ALIGN_CENTER);
style2.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(CellStyle.BORDER_THIN);
style2.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
style2.setBorderLeft(CellStyle.BORDER_THIN);
style2.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
style2.setBorderRight(CellStyle.BORDER_THIN);
style2.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
style2.setBorderTop(IndexedColors.GREY_25_PERCENT.getIndex());
for(int i=0; i< 100 ; i++){
for(int j=0; j< 100; j++){
Cell cell = sheet.createRow(i).createCell(j);
cell.setCellStyle(style1);
}
}
CellRangeAddress region = new CellRangeAddress(0, 10, 0, 10);
cleanBeforeMergeOnValidCells(sheet, region, style2);
sheet.addMergedRegion(region);
try {
FileOutputStream output = new FileOutputStream("tmp/rapport.xls");
workbook.write(output);
output.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void cleanBeforeMergeOnValidCells(Sheet sheet, CellRangeAddress region, CellStyle cellStyle) {
for (int rowNum = region.getFirstRow(); rowNum <= region.getLastRow(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
sheet.createRow(rowNum);
}
for (int colNum = region.getFirstColumn(); colNum <= region.getLastColumn(); colNum++) {
Cell currentCell = row.getCell(colNum);
if (currentCell == null) {
currentCell = row.createCell(colNum);
}
currentCell.setCellStyle(cellStyle);
}
}
}
}