Results 1 to 6 of 6
  1. #1
    agastheswar is offline Member
    Join Date
    Jan 2012
    Posts
    3
    Rep Power
    0

    Default Excel to Html Conversion using apache poi library

    i am getting the following errors please help me !!!!!!!!!!!!!!!!


    Exception in thread "main" java.lang.NullPointerException
    at java.io.Reader.<init>(Unknown Source)
    at java.io.InputStreamReader.<init>(Unknown Source)
    at ToHtml.printStyles(ToHtml.java:215)
    at ToHtml.printInlineStyle(ToHtml.java:199)
    at ToHtml.print(ToHtml.java:192)
    at ToHtml.printPage(ToHtml.java:175)
    at ToHtml.main(ToHtml.java:156)

    Code that i run



    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.examples.html.HSSFHtmlHelper;
    import org.apache.poi.ss.examples.html.HtmlHelper;
    import org.apache.poi.ss.examples.html.XSSFHtmlHelper;
    import org.apache.poi.ss.format.CellFormat;
    import org.apache.poi.ss.format.CellFormatResult;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    import java.io.BufferedReader;
    import java.io.Closeable;
    import java.io.FileInputStream;
    import java.io.FileWriter;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.io.PrintWriter;
    import java.util.Formatter;
    import java.util.HashMap;
    import java.util.HashSet;
    import java.util.Iterator;
    import java.util.Map;
    import java.util.Set;

    import static org.apache.poi.ss.usermodel.CellStyle.*;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatE xception;

    public class ToHtml {
    private final Workbook wb;
    private final Appendable output;
    private boolean completeHTML;
    private Formatter out;
    private boolean gotBounds;
    private int firstColumn;
    private int endColumn;
    private HtmlHelper helper;

    private static final String DEFAULTS_CLASS = "excelDefaults";
    private static final String COL_HEAD_CLASS = "colHeader";
    private static final String ROW_HEAD_CLASS = "rowHeader";

    private static final Map<Short, String> ALIGN = mapFor(ALIGN_LEFT, "left",
    ALIGN_CENTER, "center", ALIGN_RIGHT, "right", ALIGN_FILL, "left",
    ALIGN_JUSTIFY, "left", ALIGN_CENTER_SELECTION, "center");

    private static final Map<Short, String> VERTICAL_ALIGN = mapFor(
    VERTICAL_BOTTOM, "bottom", VERTICAL_CENTER, "middle", VERTICAL_TOP,
    "top");

    private static final Map<Short, String> BORDER = mapFor(BORDER_DASH_DOT,
    "dashed 1pt", BORDER_DASH_DOT_DOT, "dashed 1pt", BORDER_DASHED,
    "dashed 1pt", BORDER_DOTTED, "dotted 1pt", BORDER_DOUBLE,
    "double 3pt", BORDER_HAIR, "solid 1px", BORDER_MEDIUM, "solid 2pt",
    BORDER_MEDIUM_DASH_DOT, "dashed 2pt", BORDER_MEDIUM_DASH_DOT_DOT,
    "dashed 2pt", BORDER_MEDIUM_DASHED, "dashed 2pt", BORDER_NONE,
    "none", BORDER_SLANTED_DASH_DOT, "dashed 2pt", BORDER_THICK,
    "solid 3pt", BORDER_THIN, "dashed 1pt");

    @SuppressWarnings({"unchecked"})
    private static <K, V> Map<K, V> mapFor(Object... mapping) {
    Map<K, V> map = new HashMap<K, V>();
    for (int i = 0; i < mapping.length; i += 2) {
    map.put((K) mapping[i], (V) mapping[i + 1]);
    }
    return map;
    }


    public static ToHtml create(Workbook wb, Appendable output) {
    return new ToHtml(wb, output);
    }


    public static ToHtml create(String path, Appendable output)
    throws IOException {
    return create(new FileInputStream(path), output);
    }


    public static ToHtml create(InputStream in, Appendable output)
    throws IOException {
    try {
    Workbook wb = WorkbookFactory.create(in);
    return create(wb, output);
    } catch (InvalidFormatException e){
    throw new IllegalArgumentException("Cannot create workbook from stream", e);
    }
    }

    private ToHtml(Workbook wb, Appendable output) {
    if (wb == null)
    throw new NullPointerException("wb");
    if (output == null)
    throw new NullPointerException("output");
    this.wb = wb;
    this.output = output;
    setupColorMap();
    }

    private void setupColorMap() {
    if (wb instanceof HSSFWorkbook)
    helper = new HSSFHtmlHelper((HSSFWorkbook) wb);
    else if (wb instanceof XSSFWorkbook)
    helper = new XSSFHtmlHelper((XSSFWorkbook) wb);
    else
    throw new IllegalArgumentException(
    "unknown workbook type: " + wb.getClass().getSimpleName());
    }


    public static void main(String[] args) throws Exception {

    ToHtml toHtml = create("C:\\Users\\Agastheswar\\Desktop\\cc.xls", new PrintWriter(new FileWriter(args[0])));
    toHtml.setCompleteHTML(true);
    toHtml.printPage();
    }

    public void setCompleteHTML(boolean completeHTML) {
    this.completeHTML = completeHTML;
    }

    public void printPage() throws IOException {
    try {
    ensureOut();
    if (completeHTML) {
    out.format(
    "<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>%n");
    out.format("<html>%n");
    out.format("<head>%n");
    out.format("</head>%n");
    out.format("<body>%n");
    }

    print();

    if (completeHTML) {
    out.format("</body>%n");
    out.format("</html>%n");
    }
    } finally {
    if (out != null)
    out.close();
    if (output instanceof Closeable) {
    Closeable closeable = (Closeable) output;
    closeable.close();
    }
    }
    }

    public void print() {
    printInlineStyle();
    printSheets();
    }

    private void printInlineStyle() {
    //out.format("<link href=\"excelStyle.css\" rel=\"stylesheet\" type=\"text/css\">%n");
    out.format("<style type=\"text/css\">%n");
    printStyles();
    out.format("</style>%n");
    }

    private void ensureOut() {
    if (out == null)
    out = new Formatter(output);
    }

    public void printStyles() {
    ensureOut();

    // First, copy the base css
    BufferedReader in = null;
    try {
    in = new BufferedReader(new InputStreamReader(
    getClass().getResourceAsStream("excelStyle.css"))) ;
    String line;
    while ((line = in.readLine()) != null) {
    out.format("%s%n", line);
    }
    } catch (IOException e) {
    throw new IllegalStateException("Reading standard css", e);
    } finally {
    if (in != null) {
    try {
    in.close();
    } catch (IOException e) {
    //noinspection ThrowFromFinallyBlock
    throw new IllegalStateException("Reading standard css", e);
    }
    }
    }

    // now add css for each used style
    Set<CellStyle> seen = new HashSet<CellStyle>();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
    Sheet sheet = wb.getSheetAt(i);
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
    Row row = rows.next();
    for (Cell cell : row) {
    CellStyle style = cell.getCellStyle();
    if (!seen.contains(style)) {
    printStyle(style);
    seen.add(style);
    }
    }
    }
    }
    }

    private void printStyle(CellStyle style) {
    out.format(".%s .%s {%n", DEFAULTS_CLASS, styleName(style));
    styleContents(style);
    out.format("}%n");
    }

    private void styleContents(CellStyle style) {
    styleOut("text-align", style.getAlignment(), ALIGN);
    styleOut("vertical-align", style.getAlignment(), VERTICAL_ALIGN);
    fontStyle(style);
    borderStyles(style);
    helper.colorStyles(style, out);
    }

    private void borderStyles(CellStyle style) {
    styleOut("border-left", style.getBorderLeft(), BORDER);
    styleOut("border-right", style.getBorderRight(), BORDER);
    styleOut("border-top", style.getBorderTop(), BORDER);
    styleOut("border-bottom", style.getBorderBottom(), BORDER);
    }

    private void fontStyle(CellStyle style) {
    Font font = wb.getFontAt(style.getFontIndex());

    if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_NORMAL)
    out.format(" font-weight: bold;%n");
    if (font.getItalic())
    out.format(" font-style: italic;%n");

    int fontheight = font.getFontHeightInPoints();
    if (fontheight == 9) {
    //fix for stupid ol Windows
    fontheight = 10;
    }
    out.format(" font-size: %dpt;%n", fontheight);

    // Font color is handled with the other colors
    }

    private String styleName(CellStyle style) {
    if (style == null)
    style = wb.getCellStyleAt((short) 0);
    StringBuilder sb = new StringBuilder();
    Formatter fmt = new Formatter(sb);
    fmt.format("style_%02x", style.getIndex());
    return fmt.toString();
    }

    private <K> void styleOut(String attr, K key, Map<K, String> mapping) {
    String value = mapping.get(key);
    if (value != null) {
    out.format(" %s: %s;%n", attr, value);
    }
    }

    private static int ultimateCellType(Cell c) {
    int type = c.getCellType();
    if (type == Cell.CELL_TYPE_FORMULA)
    type = c.getCachedFormulaResultType();
    return type;
    }

    private void printSheets() {
    ensureOut();
    Sheet sheet = wb.getSheetAt(0);
    printSheet(sheet);
    }

    public void printSheet(Sheet sheet) {
    ensureOut();
    out.format("<table class=%s>%n", DEFAULTS_CLASS);
    printCols(sheet);
    printSheetContent(sheet);
    out.format("</table>%n");
    }

    private void printCols(Sheet sheet) {
    out.format("<col/>%n");
    ensureColumnBounds(sheet);
    for (int i = firstColumn; i < endColumn; i++) {
    out.format("<col/>%n");
    }
    }

    private void ensureColumnBounds(Sheet sheet) {
    if (gotBounds)
    return;

    Iterator<Row> iter = sheet.rowIterator();
    firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
    endColumn = 0;
    while (iter.hasNext()) {
    Row row = iter.next();
    short firstCell = row.getFirstCellNum();
    if (firstCell >= 0) {
    firstColumn = Math.min(firstColumn, firstCell);
    endColumn = Math.max(endColumn, row.getLastCellNum());
    }
    }
    gotBounds = true;
    }

    private void printColumnHeads() {
    out.format("<thead>%n");
    out.format(" <tr class=%s>%n", COL_HEAD_CLASS);
    out.format(" <th class=%s>&#x25CA;</th>%n", COL_HEAD_CLASS);
    //noinspection UnusedDeclaration
    StringBuilder colName = new StringBuilder();
    for (int i = firstColumn; i < endColumn; i++) {
    colName.setLength(0);
    int cnum = i;
    do {
    colName.insert(0, (char) ('A' + cnum % 26));
    cnum /= 26;
    } while (cnum > 0);
    out.format(" <th class=%s>%s</th>%n", COL_HEAD_CLASS, colName);
    }
    out.format(" </tr>%n");
    out.format("</thead>%n");
    }

    private void printSheetContent(Sheet sheet) {
    printColumnHeads();

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
    Row row = rows.next();

    out.format(" <tr>%n");
    out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS,
    row.getRowNum() + 1);
    for (int i = firstColumn; i < endColumn; i++) {
    String content = "&nbsp;";
    String attrs = "";
    CellStyle style = null;
    if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
    Cell cell = row.getCell(i);
    if (cell != null) {
    style = cell.getCellStyle();
    attrs = tagStyle(cell, style);
    //Set the value that is rendered for the cell
    //also applies the format
    CellFormat cf = CellFormat.getInstance(
    style.getDataFormatString());
    CellFormatResult result = cf.apply(cell);
    content = result.text;
    if (content.equals(""))
    content = "&nbsp;";
    }
    }
    out.format(" <td class=%s %s>%s</td>%n", styleName(style),
    attrs, content);
    }
    out.format(" </tr>%n");
    }
    out.format("</tbody>%n");
    }

    private String tagStyle(Cell cell, CellStyle style) {
    if (style.getAlignment() == ALIGN_GENERAL) {
    switch (ultimateCellType(cell)) {
    case HSSFCell.CELL_TYPE_STRING:
    return "style=\"text-align: left;\"";
    case HSSFCell.CELL_TYPE_BOOLEAN:
    case HSSFCell.CELL_TYPE_ERROR:
    return "style=\"text-align: center;\"";
    case HSSFCell.CELL_TYPE_NUMERIC:
    default:
    // "right" is the default
    break;
    }
    }
    return "";
    }
    }

  2. #2
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,188
    Rep Power
    19

    Default Re: Excel to Html Conversion using apache poi library

    Moved from Advanced Java. Whatever made you think that inability to resolve a NullPointerException was in any way an 'advanced' topic?

    Also, scout around the FAQs and other sections of the site and learn how to use code tags to maintain the readability of your code.

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  3. #3
    Rdhlhgeg is offline Member
    Join Date
    May 2014
    Posts
    2
    Rep Power
    0

    Default Re: Excel to Html Conversion using apache poi library

    What a complex codes sample for excel conversion. Is it really so compliate to convert excel to html web page. I know it is easy to convert pdf to html page with vb codes, so, I guess, maybe it will be a little easier to convert excel to html file with vb or c sharp codes.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,802
    Rep Power
    19

    Default Re: Excel to Html Conversion using apache poi library

    Please stop resurrecting old threads to spam a link about VB Excel conversion.
    This is a Java forum, and so people generally look for a Java solution.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    kneitzel is offline Senior Member
    Join Date
    Feb 2014
    Posts
    448
    Rep Power
    1

    Default Re: Excel to Html Conversion using apache poi library

    And just a little addition:
    On windows eith .Net it is quite easy to read data from an excel file:
    The ms access database driver can also read excel files so it is as simple as reading data from a database. So when using .Net there is no additional 3rd party library required! (Just wanted to add this in case someone who is not using java comed across this thread.) And if someone needs help on this: the forums at msdn.microsoft.com are a good place to ask for help.

    But AFAIK this does not help when using java but maybe I am wrong and someone wrote a jni extension for it.

  6. #6
    gimbal2 is online now Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,686
    Rep Power
    5

    Default Re: Excel to Html Conversion using apache poi library

    Quote Originally Posted by kneitzel View Post
    And just a little addition:
    On windows eith .Net it is quite easy to read data from an excel file:
    The ms access database driver can also read excel files so it is as simple as reading data from a database. So when using .Net there is no additional 3rd party library required! (Just wanted to add this in case someone who is not using java comed across this thread.) And if someone needs help on this: the forums at msdn.microsoft.com are a good place to ask for help.

    But AFAIK this does not help when using java but maybe I am wrong and someone wrote a jni extension for it.
    "Use the right tool for the job". Yep, .NET is certainly a better tool for this job. Alas, Java developers have to cope with the third party stuff available to them. Mentioning .NET as an alternative solution in a Java forum is basically like dangling a piece of candy in front of a kid only to reveal its a cat turd in a candy wrapper.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

Similar Threads

  1. Replies: 0
    Last Post: 01-24-2012, 12:58 PM
  2. Replies: 0
    Last Post: 11-17-2011, 09:33 AM
  3. Replies: 0
    Last Post: 09-22-2011, 12:02 PM
  4. Replies: 0
    Last Post: 10-29-2010, 11:25 AM
  5. Generate Excel using Apache POI
    By sarwan in forum New To Java
    Replies: 0
    Last Post: 06-08-2010, 11:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •