Comments by Nountylegre

Nountylegre 11-Apr-19 10:06am View

Here is another sample based on this java excel library:

List<integer> data = new ArrayList<integer>();
data.add(1);
data.add(2);
data.add(3);
// ...

SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");

ExcelFile workbook = ExcelFile.load("input.xlsx");
ExcelWorksheet worksheet = workbook.getWorksheet(0);
ExcelColumn column = worksheet.getColumn("F");

for (int i = 0; i < data.size(); i++) {
Integer value = data.get(i);
column.getCell(i).setValue(value);
}

workbook.save("output.xlsx");

Nountylegre 11-Apr-19 7:25am View

Try this:

public static void main(String[] args) {

SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");

ExcelFile workbook = ExcelFile.load("input.xlsx");
ExcelWorksheet worksheet = workbook.getWorksheet(0);

// 1. Copy first row and insert it before the second row.
worksheet.getRows().insertCopy(1, worksheet.getRow(0));

// 2. Clear row data without loosing formatting.
for (ExcelCell cell : worksheet.getRow(1).getAllocatedCells()) {
cell.setValue((Object) null);
}

// 3. Set font bold, color and border.
CellRange cells = worksheet.getCells().getSubrange("A2:C2");
CellStyle style = cells.getStyle();
style.getFont().setWeight(ExcelFont.BOLD_WEIGHT);
style.getFont().setColor(SpreadsheetColor.fromName(ColorName.BLUE));
style.getBorders().setBorders(MultipleBorders.outside(), SpreadsheetColor.fromArgb(252, 1, 1), LineStyle.THIN);

workbook.save("output.xlsx");
}

The code uses this Java API for Excel files.

Nountylegre 11-Apr-19 6:54am View

You should try these out as well:
- How to write Excel file in Java
- How to read Excel file in Java

Nountylegre 8-Apr-19 6:47am View

This java library for excel spreadsheets can easily read your excel file from java like this:

ExcelFile workbook = ExcelFile.load(file);
ExcelWorksheet worksheet = workbook.getWorksheet(0);

int rows = worksheet.getRows().size();
int cols = worksheet.calculateMaxUsedColumns();

for (int row = 0; row < rows; row++) {
for (int col = 0; col < cols; col++) {
ExcelCell cell = worksheet.getCell(row, col);
System.out.println(cell.getValue());
}
}

Or iterate like this:

ExcelFile workbook = ExcelFile.load(file);
ExcelWorksheet worksheet = workbook.getWorksheet(0);

for (ExcelRow row : worksheet.getRows()) {
for (ExcelCell cell : row.getAllocatedCells()) {
System.out.println(cell.getValue());
}
}

In both ways the cell's value is retrieved as object (it can be string, number or date) and you can find out which type the value is, so you should be able to get the expected value.

Nountylegre 4-Mar-19 3:54am View

If Jexcel is not working for you, perhaps you should try with this java excel library, for instance see how to read xlsx files in java.

Nountylegre 4-Mar-19 2:18am View

One of the easy and straightforward ways to do this is using this excel api in java, like this:

ExcelFile ef = ExcelFile.load("Input.xlsx");
ef.removeWorksheet("Sheet 1");
ef.save("Output.xlsx");

Nountylegre 25-Feb-19 8:45am View

Try this Excel library for Java:

import com.gembox.spreadsheet.*;
// ...

ExcelFile ef = ExcelFile.load("SimpleTemplate.xlsx");

// Iterate through all worksheets in an Excel workbook.
for (ExcelWorksheet sheet : ef.getWorksheets()) {
// Iterate through all rows in an Excel worksheet.
for (ExcelRow row : sheet.getRows()) {
// Iterate through all allocated cells in an Excel row.
for (ExcelCell cell : row.getAllocatedCells()) {
System.out.println(cell.getValue());
}
}
}

The code is from this example for reading Excel file in Java.

Nountylegre 25-Feb-19 8:36am View

Yes, this works great, many thanks!
This is how I'm reading Excel file in Java and this is how I'm writing Excel file in Java.

Comments by Nountylegre (Top 8 by date)