Export JTable with header to Excel

3

I would like to know how I can do so that when exporting a table to Excel from Java , I can include a header such as my company name or something like that. I can export the table without problems, but I do not know how to put a header, or at least an image with the name of the company.

I am using this code to export.

import java.io.*;
import java.util.List;
import javax.swing.*;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import jxl.*;

public class export_excel {

    private File archi;
    private List<JTable> tabla;
    private List<String> nom_hoja;
    private WritableCellFormat  fomato_fila ;
    private WritableCellFormat  fomato_columna;

    public export_excel(List<JTable> tab, File ar) throws Exception {
        this.archi = ar;
        this.tabla = tab;
        if(tab.size()<0){
            throw new Exception("ERROR");
        }
    }
    public boolean export() {
        try {
            DataOutputStream out = new DataOutputStream(new FileOutputStream(archi));
            WritableWorkbook w = Workbook.createWorkbook(out);
            w.createSheet("Parqueo Astor", 0);

            for (int index=0;index<tabla.size();index++) {
                JTable table=tabla.get(index);

                  WritableSheet s = w.getSheet(0);

                for (int i = 0; i < table.getColumnCount(); i++) {
                    for (int j = 0; j < table.getRowCount(); j++) {
                        Object objeto = table.getValueAt(j, i);

                        createColumna(s,table.getColumnName(i),i);//crea la columna
                        createFilas(s,i,j,String.valueOf(objeto));//crea las filas
                    }
                }
            }
            w.write();
            w.close();
            out.close();
            return true;

        } catch (IOException ex) {
            ex.printStackTrace();
        } catch (WriteException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    private void createColumna(WritableSheet sheet,String columna,int number_columna)throws WriteException {
        //creamos el tipo de letra
        WritableFont times10pt = new WritableFont(WritableFont.TAHOMA, 14);
        // definimos el formato d ela celda
        WritableCellFormat  times = new WritableCellFormat(times10pt);
        // Permite si se ajusta automáticamente a las células
        //times.setWrap(true);
        // crea una negrita con subrayado
        WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TAHOMA, 11, WritableFont.BOLD, false,UnderlineStyle.SINGLE);
        fomato_columna = new WritableCellFormat(times10ptBoldUnderline);
        // Permite que se ajusta automáticamente a las células
        //fomato_columna.setWrap(true);
        CellView cevell = new CellView();
        cevell.setSize(920);
        cevell.setDimension(70);
        cevell.setFormat(times);
        cevell.setFormat(fomato_columna);
        //cevell.setAutosize(true);
        // escribimos las columnas
        addColumna(sheet, number_columna, 0, columna,fomato_columna);//numero de columna , 0 es la fila
    }
    /****************************************/
    private void createFilas(WritableSheet sheet,int number_columna,int filas,String name_filas)throws WriteException {
        //creamos el tipo de letra
        WritableFont times10pt = new WritableFont(WritableFont.ARIAL, 12);
        times10pt.setColour(Colour.GOLD);
        // definimos el formato d ela celda
        WritableCellFormat  times = new WritableCellFormat(times10pt);
        times.setBorder(Border.TOP, BorderLineStyle.MEDIUM, Colour.GOLD);
        // Permite si se ajusta automáticamente a las células
        //times.setWrap(true);
        // crea una negrita con subrayado
        WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE);
        fomato_fila = new WritableCellFormat(times10ptBoldUnderline);
        // Permite que se ajusta automáticamente a las células
        //fomato_fila.setWrap(true);
        CellView cevell = new CellView();
        cevell.setDimension(70);
        cevell.setFormat(times);
        cevell.setFormat(fomato_fila);
        //cevell.setAutosize(true);
        // escribimos las columnas
        addFilas(sheet, number_columna, filas, name_filas,fomato_fila);
    }
    /***********************************/
    private void addColumna(WritableSheet sheet, int column, int row, String s,WritableCellFormat format)throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, format);
        sheet.addCell(label);
    }
    private void addFilas(WritableSheet sheet, int column, int row, String s,WritableCellFormat format)throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, format);
        sheet.addCell(label);
    }
}

... and to call the class I use this function.

public void exportartabla(){
    try { 
        creardirectorio();
        List<JTable> tb = new ArrayList<JTable>();
        tb.add(jTable1);
        //-------------------
        export_excel excelExporter = new export_excel(tb, new File(ubic+".xls")); // String con directorio
        if (excelExporter.export()) {
            System.out.println("TABLAS EXPORTADOS CON EXITOS!");
        }
    }catch (Exception ex) {
        ex.printStackTrace();
    }
}
    
asked by Daylight Ark 30.12.2016 в 02:58
source

1 answer

2

The library has some methods for it. Example:

  HeaderFooter header = new HeaderFooter();
  header.getLeft().appendWorkbookName();
  header.getCentre().append("ENCABEZADO");
  header.getRight().appendWorkSheetName();
  sheet.getSettings().setHeader(header);

You can also enter text at the bottom of the page if you wish.

Complete example:

import java.io.File;
import java.io.IOException;

import jxl.HeaderFooter;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class SheetHeaderFooterTest {

  /**
   * @param args
   * @throws IOException 
   * @throws IOException 
   * @throws WriteException 
   * @throws BiffException 
   */
  public static void main(String[] args) throws IOException, WriteException {
    //Creates a writable workbook with the given file name
    WritableWorkbook workbook = Workbook.createWorkbook(new File("C:/JXL/HeaderFooter.xls"));

    //Create sheet and add a label
    WritableSheet sheet = workbook.createSheet("My Sheet", 0);

    //Add Header and Footer
      HeaderFooter header = new HeaderFooter();
      header.getLeft().appendWorkbookName();
      header.getCentre().append("BE THE CODER");
      header.getRight().appendWorkSheetName();
      sheet.getSettings().setHeader(header);

      HeaderFooter footer = new HeaderFooter();
      footer.getLeft().appendDate();

      footer.getCentre().append("Page ");
      footer.getCentre().appendPageNumber();
      footer.getCentre().append("/");
      footer.getCentre().appendTotalPages();

      footer.getRight().appendTime();
      sheet.getSettings().setFooter(footer);


    sheet.addCell(new Label(1, 2, "ABCD"));
    sheet.addCell(new Label(1, 200, "ABCD"));

    //Writes out the data held in this workbook in Excel format
    workbook.write(); 

    //Close and free allocated memory 
    workbook.close(); 
  }

}

Source of previous code: Here

See also the Library documentation jxl .

    
answered by 14.02.2017 в 15:33