Header Ads

  • Breaking Now

    Write Into An Excel File Using Java

    In order to write content to an MS Excel sheet you are required to download JExcel APIs from here.

    package example;

    import java.io.File;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Locale;
    import java.util.Properties;

    import jxl.Workbook;
    import jxl.WorkbookSettings;
    import jxl.format.Colour;
    import jxl.write.Label;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;

    public class JExcelExample {
    int mCount = 0;

    public static void main(String[] args) {
    try {
    boolean isOutputFolder = false;
    JExcelExample jexcel = new JExcelExample();
    Properties prop = jexcel.loadProperties();
    String sheets = prop.getProperty("org.sheets");
    File directory = new File(prop.getProperty("output.folder"));
    if (!directory.exists()) {
    isOutputFolder = directory.mkdir();
    } else {
    isOutputFolder = true;
    }

    if (!isOutputFolder) {
    System.out.println("Please provide output folder name");
    System.exit(0);
    }

    String filename = prop.getProperty("output.filename");
    WorkbookSettings ws = new WorkbookSettings();
    ws.setLocale(new Locale("en", "EN"));
    WritableWorkbook workbook = Workbook.createWorkbook(new File(
    directory.getAbsolutePath() + "\\" + filename), ws);
    int count = Integer.parseInt(sheets);
    for (int i = 1; i <= count; i++) {
    WritableSheet sheet = workbook.createSheet(prop
    .getProperty("org.sheet[" + i + "]"), i);
    writeDataSheet(sheet);

    }

    workbook.write();
    workbook.close();
    } catch (WriteException e) {
    e.printStackTrace();
    } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }

    private static void writeDataSheet(WritableSheet aSheet)
    throws WriteException {
    JExcelExample jExcelExample = new JExcelExample();
    Properties properties = jExcelExample.loadProperties();
    WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
    WritableFont.BOLD);
    // WritableFont wf2 = new WritableFont(WritableFont.ARIAL, 10,
    // WritableFont.NO_BOLD);
    WritableCellFormat cf = new WritableCellFormat(wf);
    cf.setBackground(Colour.ORANGE);
    // WritableCellFormat cf2 = new WritableCellFormat(wf2);
    String columnValue = properties.getProperty(aSheet.getName()
    + ".coulumn");
    int columns = Integer.parseInt(columnValue);

    for (int j = 1; j < columns + 1; j++) {
    String coulmnValue = properties.getProperty(aSheet.getName()
    + ".coulumn.value[" + j + "]");
    aSheet.addCell(new Label(j - 1, 0, coulmnValue, cf));
    }

    }

    /**
    *
    * @return
    */

    private Properties loadProperties() {
    Properties prop = new Properties();

    try {
    InputStream is = JExcelExample.class.getClassLoader()
    .getResourceAsStream("excel.properties");
    prop.load(is);
    } catch (IOException ioe) {
    ioe.printStackTrace();
    }
    return prop;
    }

    }


    The content of excel.properties file looks like as given below:

    org.sheets=2

    org.sheet[1]=Employee
    org.sheet[2]=Department


    Employee.coulumn=7
    Department.coulumn=12

    #Columns of Employee sheet
    Employee.coulumn.value[1]=First Name
    Employee.coulumn.value[2]=Last Name
    Employee.coulumn.value[3]=Address 1
    Employee.coulumn.value[4]=Address 2
    Employee.coulumn.value[5]=City
    Employee.coulumn.value[6]=State
    Employee.coulumn.value[7]=Contact Number

    #Columns of Department sheet
    Department.coulumn.value[1]=Marketing and Sales
    Department.coulumn.value[2]=HR
    Department.coulumn.value[3]=Finance
    Department.coulumn.value[4]=IT
    Department.coulumn.value[5]=Procurement and Inventory
    Department.coulumn.value[6]=Legal and Taxation
    Department.coulumn.value[7]=Risk Management Office
    Department.coulumn.value[8]=Adminstration
    Department.coulumn.value[9]=Security
    Department.coulumn.value[10]=Logistics
    Department.coulumn.value[11]=Communication
    Department.coulumn.value[12]=Knowledge Management

    #Output XL Sheet path
    output.folder=c:\\TestXL
    output.filename=output.xls

    No comments

    Post Top Ad

    Post Bottom Ad