My code writes data to excel however it is overwriting the same row and columns. I do not want to overwrite the row that has the column titles and the rows after that if it has data in it.
Steps 1 enter column heading 2 enter data in rows/columns 3 if column heading are there don't write column heading 4 if row/columns filled go to next 5 repeat 1-4
My code is below. Any help would be appreciated.
//Create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
//Create a blank sheet
XSSFSheet spreadsheet = workbook.createSheet( " Employee Info ");
//Create row object
XSSFRow row;
//This data needs to be written (Object[])
Map < String, Object[] > clientinfo = new TreeMap < String, Object[] >();
clientinfo.put( "1", new Object[] {
"PayRoll Status", "Week #", "Pay Date","End Date" });
clientinfo.put( "2", new Object[] {
CycleStatus, WeekID, DateId, EndDateId });
//Iterate over data and write to sheet
Set < String > keyid = clientinfo.keySet();
int rowid = 0;
for (String key : keyid) {
row = spreadsheet.createRow(rowid++);
Object [] objectArr = clientinfo.get(key);
int cellid = 0;
for (Object obj : objectArr){
Cell cell = row.createCell(cellid++);
cell.setCellValue((String)obj);
}
}
//Write the workbook in file system
FileOutputStream out = new FileOutputStream(
new File("C:/PayrollSync/Pre-Payroll.xlsx"));
workbook.write(out);
out.close();
System.out.println("Prepayroll.xlsx written successfully");
This is the solution i came up with however it write the count of the rows is there any way to take it off
String excelFilePath = "C://PayrollSync//Pre-Payroll.xlsx";
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//CycleStatus, WeekID, DateId, EndDateId
Object[][] bookData = {
{CycleStatus,WeekID,DateId,EndDateId},
};
int rowCount = sheet.getLastRowNum();
for (Object[] aBook : bookData) {
Row row = sheet.createRow(++rowCount);
int columnCount = 0;
Cell cell = row.createCell(columnCount);
cell.setCellValue(rowCount);
for (Object field : aBook) {
cell = row.createCell(++columnCount);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}
}
}
FileOutputStream outputStream = new FileOutputStream("C://PayrollSync//Pre-Payroll.xlsx");
workbook.write(outputStream);
inputStream.close();
outputStream.close();
I'll do it in this way:
import com.google.common.collect.Lists;
import no.tollpost.web.termodash.rest.report.excel.model.ExcelReportDTO;
import no.tollpost.web.termodash.rest.report.excel.model.RowDTO;
import org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.IntStream;
public final class ExcelGenerator {
private ExcelGenerator() {
}
public static void getExcelVersion(final OutputStream outputStream) {
try {
final Workbook book = new XSSFWorkbook();
generateReport(book);
book.write(outputStream);
} catch (final IOException | OpenXML4JRuntimeException e) {
//log.warn("Failed to build excel report", e);
}
}
private static void generateReport(final Workbook book) {
final Sheet sheet = book.createSheet("Sheet_name");
final List<String> columnNames = getColumnNames();
final List<RowDTO> rows = getRows(getYourData()); // get your data as list of your DTOs
generateTableHeader(sheet, book, columnNames);
generateTableRows(sheet, rows);
IntStream.range(0, columnNames.size()).forEach(sheet::autoSizeColumn);
}
private List<YourDTO> getYourData() {
return // Your data as List (each element will be row in the excel sheet)
}
private static List<String> getColumnNames() {
return Lists.newArrayList("column_name_1", "column_name_2", "column_name_3");
}
private static List<RowDTO> getRows(final List<YourDTO> data) {
final List<RowDTO> rows = new ArrayList<>();
IntStream.range(0, data.size()).forEach(i -> {
final List<String> cellsMapper = new ArrayList<>();
cellsMapper.add(data.get(i).getSomeProperty());
cellsMapper.add(data.get(i).getOtherProperty());
cellsMapper.add(data.get(i).getThirdProperty());
cellsMapper.add(data.get(i).getFourthProperty());
rows.add(new RowDTO(cellsMapper));
});
// rows.sort(Comparator.comparing(row -> row.getData().get(3)));
return rows;
}
private static void generateTableHeader(final Sheet sheet, final Workbook workbook, final List<String> cellTitles) {
final Row tabellHeader = sheet.createRow(0);
IntStream.range(0, cellTitles.size()).forEach(cellNummer -> {
final Cell cell = tabellHeader.createCell(cellNummer);
cell.setCellValue(cellTitles.get(cellNummer));
setHeaderCellStyle(cell, workbook, cellTitles.get(cellNummer));
});
}
private static void setHeaderCellStyle(final Cell celle, final Workbook workbook, final String celleTittel) {
if (!celleTittel.isEmpty()) {
celle.setCellStyle(getHeaderCellStyle(workbook));
}
}
private static CellStyle getHeaderCellStyle(final Workbook workbook) {
final CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
final Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
return style;
}
private static void generateTableRows(final Sheet sheet, final List<RowDTO> rows) {
IntStream.range(0, rows.size()).forEach(rowNUmber -> {
final Row row = sheet.createRow(rowNUmber + 1);
IntStream.range(0, rows.get(rowNUmber).getData().size())
.forEach(cellNumber -> setCellValue(cellNumber, rowNUmber, row, rows));
});
}
private static void setCellValue(final int cellNumber, final int rowNumber, final Row row, final List<RowDTO> rows) {
final Cell cell = row.createCell(cellNumber);
cell.setCellValue(rows.get(rowNumber).getData().get(cellNumber));
cell.setCellType(Cell.CELL_TYPE_STRING);
}
}
final class RowDTO {
private final List<String> data;
public RowDTO(final List<String> data) {
this.data = data;
}
public List<String> getData() {
return data;
}
}