Create Excel file in Java

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I used also JXLS  it receives the data as a Map and a template EXCEL with the correct syntax and return the file correctly populated  The data in every cell must be a JavaBean with visibility public   It not worws if you must insert data in more than 1 sheet  in this case I used POI

User · Answer

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  sankumarsingh gmail com                 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

User · Answer

I ve created the API  quot generator-excel quot  to create an Excel file  below the dependecy    lt dependency gt     lt groupId gt com github bld-commons excel lt  groupId gt     lt artifactId gt generator-excel lt  artifactId gt     lt version gt 3 1 0 lt  version gt   lt  dependency gt   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  I created 2 classes that represents the row of the table  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    quot Id quot   indexColumn   0           ExcelCellLayout horizontalAlignment   HorizontalAlignment RIGHT          private Integer idUtente            ExcelColumn columnName    quot Nome quot   indexColumn   2           ExcelCellLayout         private String nome            ExcelColumn columnName    quot Cognome quot   indexColumn   1           ExcelCellLayout         private String cognome           ExcelColumn columnName    quot Data di nascita quot   indexColumn   3           ExcelCellLayout horizontalAlignment   HorizontalAlignment CENTER           ExcelDate         private Date dataNascita           ExcelColumn columnName    quot Immagine quot   indexColumn   4           ExcelCellLayout          ExcelImage resizeHeight   0 7  resizeWidth   0 6          private byte   image                       ExcelColumn columnName    quot Path quot   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    quot Total quot  endLabel    quot total quot   public class SalaryRow implements RowSheet         ExcelColumn columnName    quot Name quot   indexColumn   0       ExcelCellLayout     private String name       ExcelColumn columnName    quot Amount quot   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                I created 2 class that represents the sheets  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    quot SELECT id utente  nome  cognome  data nascita image path  quot             quot FROM utente  quot             quot WHERE cognome  cognome  quot             quot order by cognome nome quot   public class UtenteSheet extends QuerySheetData lt UtenteRow gt              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 lt SalaryRow gt         public SalarySheet  Size max   31  String sheetName            super sheetName             Class test  in the test function there are antoher sheets 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     quot bld generator quot   quot bld read quot     EnableTransactionManagement public class ReportTestJpa            The Constant PATH FILE         private static final String PATH FILE    quot  mnt report  quot            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 lt BaseSheet gt  listBaseSheet   new ArrayList lt  gt                      UtenteSheet utenteSheet new UtenteSheet  quot Utente quot            utenteSheet getMapParameters   put  quot cognome quot    quot Rossi quot            listBaseSheet add utenteSheet                    CasaEditrice casaEditrice   new CasaEditrice  quot Casa Editrice quot   quot Mondadori quot   new GregorianCalendar 1955  Calendar MAY  10    quot Roma quot    quot  home francesco Documents git-project dev-excel linux jpg quot   quot Drammatico quot            listBaseSheet add casaEditrice                             AutoreLibriSheet autoreLibriSheet   new AutoreLibriSheet  quot Libri d autore quot   quot Test label quot            TotaleAutoreLibriSheet totaleAutoreLibriSheet new TotaleAutoreLibriSheet            totaleAutoreLibriSheet getListRowSheet   add new TotaleAutoreLibriRow  quot Totale quot             autoreLibriSheet setSheetFunctionsTotal totaleAutoreLibriSheet           listBaseSheet add autoreLibriSheet           GenereSheet genereSheet new GenereSheet  quot Genere quot            listBaseSheet add genereSheet           SalarySheet salarySheet new SalarySheet  quot salary quot            salarySheet getListRowSheet   add new SalaryRow  quot a quot  2 0            salarySheet getListRowSheet   add new SalaryRow  quot a quot  2 0            salarySheet getListRowSheet   add new SalaryRow  quot a quot  2 0            salarySheet getListRowSheet   add new SalaryRow  quot a quot  2 0            salarySheet getListRowSheet   add new SalaryRow  quot c quot  1 0            salarySheet getListRowSheet   add new SalaryRow  quot c quot  1 0            salarySheet getListRowSheet   add new SalaryRow  quot c quot  1 0            salarySheet getListRowSheet   add new SalaryRow  quot c quot  1 0            listBaseSheet add salarySheet           ReportExcel excel   new ReportExcel  quot Mondadori JPA quot   listBaseSheet            byte   byteReport   this generateExcel createFileXlsx excel            ExcelUtils writeToFile PATH FILE excel getTitle     quot  xlsx quot   byteReport                    Application yaml 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   https   github com bld-commons dev-excel

User · Answer

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

User · Answer

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  lt b gt  tags to do what you ask for

User · Answer

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

User · Answer

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

[java] Create Excel file in Java

Examples related to java

Examples related to excel

Examples related to jexcelapi