I want to create an Excel file and write data just like writing a text file with Java. I tried to change file extension from .txt
to .xls
. But I want to bold letters in the Excel file. How can I do that?
I have tried using the JXL API, but every time I have to create a label I want add no label. Can't O edit row and column of the table?
File fileName = new File(".....\\Fund.xlsx");
public static void createWorkbook(File fileName) throws IOException {
try {
FileOutputStream fos = new FileOutputStream(fileName);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("fund");
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
cell0.setCellValue("Nav Value");
Cell cell1 = row.createCell(1);
cell1.setCellValue("Amount Change");
Cell cell2 = row.createCell(2);
cell2.setCellValue("Percent Change");
workbook.write(fos);
fos.flush();
fos.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
You can use Apache POI for creating native binary xls files.
Or you can use JExcelApi which is another, and somewhat light-weight as far as I can remember, Java library for Excel.
To create a spreadsheet and format a cell using POI, see the Working with Fonts example, and use:
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
POI works very well. There are some things you can't do (e.g. create VBA macros), but it'll read/write spreadsheets with macros, so you can create a suitable template sheet, read it and manipulate it with POI, and then write it out.
Changing the extension of a file does not in any way change its contents. The extension is just a label.
If you want to work with Excel spreadsheets using Java, read up on the Apache POI library.
I've created an API to create an Excel file more easier.
Create Excel - Creating Excel from Template
Just set the required values upon instantiation then invoke execute(), it will be created based on your desired output directory.
But before you use this, you must have an Excel Template which will be use as a template of the newly created Excel file.
Also, you need Apache POI in your project's class path.
I've created the API "generator-excel" to create an Excel file, below the dependecy:
<dependency>
<groupId>com.github.bld-commons.excel</groupId>
<artifactId>generator-excel</artifactId>
<version>3.1.0</version>
</dependency>
This library can to configure the styles, the functions, the charts, the pivot table and etc. through a series of annotations.
You can write rows by getting data from a datasource trough a query with or without parameters.
Below an example to develop
package bld.generator.report.junit.entity;
import java.util.Date;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import bld.generator.report.excel.RowSheet;
import bld.generator.report.excel.annotation.ExcelCellLayout;
import bld.generator.report.excel.annotation.ExcelColumn;
import bld.generator.report.excel.annotation.ExcelDate;
import bld.generator.report.excel.annotation.ExcelImage;
import bld.generator.report.excel.annotation.ExcelRowHeight;
@ExcelRowHeight(height = 3)
public class UtenteRow implements RowSheet {
@ExcelColumn(columnName = "Id", indexColumn = 0)
@ExcelCellLayout(horizontalAlignment = HorizontalAlignment.RIGHT)
private Integer idUtente;
@ExcelColumn(columnName = "Nome", indexColumn = 2)
@ExcelCellLayout
private String nome;
@ExcelColumn(columnName = "Cognome", indexColumn = 1)
@ExcelCellLayout
private String cognome;
@ExcelColumn(columnName = "Data di nascita", indexColumn = 3)
@ExcelCellLayout(horizontalAlignment = HorizontalAlignment.CENTER)
@ExcelDate
private Date dataNascita;
@ExcelColumn(columnName = "Immagine", indexColumn = 4)
@ExcelCellLayout
@ExcelImage(resizeHeight = 0.7, resizeWidth = 0.6)
private byte[] image;
@ExcelColumn(columnName = "Path", indexColumn = 5)
@ExcelCellLayout
@ExcelImage(resizeHeight = 0.7, resizeWidth = 0.6)
private String path;
public UtenteRow() {
}
public UtenteRow(Integer idUtente, String nome, String cognome, Date dataNascita) {
super();
this.idUtente = idUtente;
this.nome = nome;
this.cognome = cognome;
this.dataNascita = dataNascita;
}
public Integer getIdUtente() {
return idUtente;
}
public void setIdUtente(Integer idUtente) {
this.idUtente = idUtente;
}
public String getNome() {
return nome;
}
public void setNome(String nome) {
this.nome = nome;
}
public String getCognome() {
return cognome;
}
public void setCognome(String cognome) {
this.cognome = cognome;
}
public Date getDataNascita() {
return dataNascita;
}
public void setDataNascita(Date dataNascita) {
this.dataNascita = dataNascita;
}
public byte[] getImage() {
return image;
}
public String getPath() {
return path;
}
public void setImage(byte[] image) {
this.image = image;
}
public void setPath(String path) {
this.path = path;
}
}
package bld.generator.report.junit.entity;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import bld.generator.report.excel.RowSheet;
import bld.generator.report.excel.annotation.ExcelCellLayout;
import bld.generator.report.excel.annotation.ExcelColumn;
import bld.generator.report.excel.annotation.ExcelFont;
import bld.generator.report.excel.annotation.ExcelSubtotal;
import bld.generator.report.excel.annotation.ExcelSubtotals;
@ExcelSubtotals(labelTotalGroup = "Total",endLabel = "total")
public class SalaryRow implements RowSheet {
@ExcelColumn(columnName = "Name", indexColumn = 0)
@ExcelCellLayout
private String name;
@ExcelColumn(columnName = "Amount", indexColumn = 1)
@ExcelCellLayout(horizontalAlignment = HorizontalAlignment.RIGHT)
@ExcelSubtotal(dataConsolidateFunction = DataConsolidateFunction.SUM,excelCellLayout = @ExcelCellLayout(horizontalAlignment = HorizontalAlignment.RIGHT,font=@ExcelFont(bold = true)))
private Double amount;
public SalaryRow() {
super();
}
public SalaryRow(String name, Double amount) {
super();
this.name = name;
this.amount = amount;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getAmount() {
return amount;
}
public void setAmount(Double amount) {
this.amount = amount;
}
}
package bld.generator.report.junit.entity;
import javax.validation.constraints.Size;
import bld.generator.report.excel.QuerySheetData;
import bld.generator.report.excel.annotation.ExcelHeaderLayout;
import bld.generator.report.excel.annotation.ExcelMarginSheet;
import bld.generator.report.excel.annotation.ExcelQuery;
import bld.generator.report.excel.annotation.ExcelSheetLayout;
@ExcelSheetLayout
@ExcelHeaderLayout
@ExcelMarginSheet(bottom = 1.5, left = 1.5, right = 1.5, top = 1.5)
@ExcelQuery(select = "SELECT id_utente, nome, cognome, data_nascita,image,path "
+ "FROM utente "
+ "WHERE cognome=:cognome "
+ "order by cognome,nome")
public class UtenteSheet extends QuerySheetData<UtenteRow> {
public UtenteSheet(@Size(max = 31) String sheetName) {
super(sheetName);
}
}
package bld.generator.report.junit.entity;
import javax.validation.constraints.Size;
import bld.generator.report.excel.SheetData;
import bld.generator.report.excel.annotation.ExcelHeaderLayout;
import bld.generator.report.excel.annotation.ExcelMarginSheet;
import bld.generator.report.excel.annotation.ExcelSheetLayout;
@ExcelSheetLayout
@ExcelHeaderLayout
@ExcelMarginSheet(bottom = 1.5,left = 1.5,right = 1.5,top = 1.5)
public class SalarySheet extends SheetData<SalaryRow> {
public SalarySheet(@Size(max = 31) String sheetName) {
super(sheetName);
}
}
package bld.generator.report.junit;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import bld.generator.report.excel.BaseSheet;
import bld.generator.report.excel.GenerateExcel;
import bld.generator.report.excel.data.ReportExcel;
import bld.generator.report.junit.entity.AutoreLibriSheet;
import bld.generator.report.junit.entity.CasaEditrice;
import bld.generator.report.junit.entity.GenereSheet;
import bld.generator.report.junit.entity.SalaryRow;
import bld.generator.report.junit.entity.SalarySheet;
import bld.generator.report.junit.entity.TotaleAutoreLibriRow;
import bld.generator.report.junit.entity.TotaleAutoreLibriSheet;
import bld.generator.report.junit.entity.UtenteSheet;
import bld.generator.report.utils.ExcelUtils;
/**
* The Class ReportTest.
*/
@RunWith(SpringRunner.class)
@SpringBootTest
@ConfigurationProperties
@ComponentScan(basePackages = {"bld.generator","bld.read"})
@EnableTransactionManagement
public class ReportTestJpa {
/** The Constant PATH_FILE. */
private static final String PATH_FILE = "/mnt/report/";
/** The generate excel. */
@Autowired
private GenerateExcel generateExcel;
/**
* Sets the up.
*
* @throws Exception the exception
*/
@Before
public void setUp() throws Exception {
}
/**
* Test.
*
* @throws Exception the exception
*/
@Test
public void test() throws Exception {
List<BaseSheet> listBaseSheet = new ArrayList<>();
UtenteSheet utenteSheet=new UtenteSheet("Utente");
utenteSheet.getMapParameters().put("cognome", "Rossi");
listBaseSheet.add(utenteSheet);
CasaEditrice casaEditrice = new CasaEditrice("Casa Editrice","Mondadori", new GregorianCalendar(1955, Calendar.MAY, 10), "Roma", "/home/francesco/Documents/git-project/dev-excel/linux.jpg","Drammatico");
listBaseSheet.add(casaEditrice);
AutoreLibriSheet autoreLibriSheet = new AutoreLibriSheet("Libri d'autore","Test label");
TotaleAutoreLibriSheet totaleAutoreLibriSheet=new TotaleAutoreLibriSheet();
totaleAutoreLibriSheet.getListRowSheet().add(new TotaleAutoreLibriRow("Totale"));
autoreLibriSheet.setSheetFunctionsTotal(totaleAutoreLibriSheet);
listBaseSheet.add(autoreLibriSheet);
GenereSheet genereSheet=new GenereSheet("Genere");
listBaseSheet.add(genereSheet);
SalarySheet salarySheet=new SalarySheet("salary");
salarySheet.getListRowSheet().add(new SalaryRow("a",2.0));
salarySheet.getListRowSheet().add(new SalaryRow("a",2.0));
salarySheet.getListRowSheet().add(new SalaryRow("a",2.0));
salarySheet.getListRowSheet().add(new SalaryRow("a",2.0));
salarySheet.getListRowSheet().add(new SalaryRow("c",1.0));
salarySheet.getListRowSheet().add(new SalaryRow("c",1.0));
salarySheet.getListRowSheet().add(new SalaryRow("c",1.0));
salarySheet.getListRowSheet().add(new SalaryRow("c",1.0));
listBaseSheet.add(salarySheet);
ReportExcel excel = new ReportExcel("Mondadori JPA", listBaseSheet);
byte[] byteReport = this.generateExcel.createFileXlsx(excel);
ExcelUtils.writeToFile(PATH_FILE,excel.getTitle(), ".xlsx", byteReport);
}
}
logging:
level:
root: WARN
org:
springframework:
web: DEBUG
hibernate: ERROR
spring:
datasource:
url: jdbc:postgresql://localhost:5432/excel_db
username: ${EXCEL_USER_DB}
password: ${EXCEL_PASSWORD_DB}
jpa:
show-sql: true
properties:
hibernate:
default_schema: public
jdbc:
lob:
non_contextual_creation: true
format_sql: true
ddl-auto: auto
database-platform: org.hibernate.dialect.PostgreSQLDialect
generate-ddl: true
below the link of the project on github:
Fair warning about Apache POI's Excel generation... (I know this is an old post, but it's important in case someone looks this up again like I just did)
It had a memory leak issue, which supposedly was solved by 2006, but which people quite recently have still been experiencing. If you want to automate generating a large amount of excel (i.e., if you want to generate a single, large file, a large number of small files, or both), I'd recommend using a different API. Either that, or increasing the JVM stack size to preposterous proportions, and maybe looking into interning strings if you know you won't actually be working with many different strings (although, of course, interning strings means that if you have a large number of different strings, you'll have an entirely different program-crashing memory problem. So, consider that before you go that route).
//Find jar from here "http://poi.apache.org/download.html"
import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
public class CreateExlFile{
public static void main(String[]args) {
try {
String filename = "C:/NewExcelFile.xls" ;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FirstSheet");
HSSFRow rowhead = sheet.createRow((short)0);
rowhead.createCell(0).setCellValue("No.");
rowhead.createCell(1).setCellValue("Name");
rowhead.createCell(2).setCellValue("Address");
rowhead.createCell(3).setCellValue("Email");
HSSFRow row = sheet.createRow((short)1);
row.createCell(0).setCellValue("1");
row.createCell(1).setCellValue("Sankumarsingh");
row.createCell(2).setCellValue("India");
row.createCell(3).setCellValue("[email protected]");
FileOutputStream fileOut = new FileOutputStream(filename);
workbook.write(fileOut);
fileOut.close();
workbook.close();
System.out.println("Your excel file has been generated!");
} catch ( Exception ex ) {
System.out.println(ex);
}
}
}
Flat files do not allow providing meta information.
I would suggest writing out a HTML table containing the information you need, and let Excel read it instead. You can then use <b> tags to do what you ask for.
Source: Stackoverflow.com