XLSX Apache Java 上的单元格和列 [英] Cell and Column on XLSX Apache Java

问题描述

我的代码确实有一些问题.如果你们中的任何人愿意帮助我，真的很感激.下面是我的代码和 2 个屏幕截图，显示了它的外观以及代码执行时的外观.

I really have some problems with my code. Really appreciate it if any of you would help me. Below is my code and 2 screenshots of what it looks like and how it should looks like when the code is being executed.

try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename="+ ReportID + ".xlsx");

String excelFileName = "C:\\Test.xlsx";
XSSFWorkbook w = new XSSFWorkbook();
System.out.println("w: " + w);
XSSFSheet s = w.createSheet(ReportID);
System.out.println("s: " + s);

// Report Title
s.createRow(0).createCell(0).setCellValue(Title);
System.out.println("Title: " + Title);

// Populate the worksheet
int _col_cnt = HeadersLabel.length;
XSSFRow row = s.createRow(_col_cnt);
System.out.println("HeadersLabel: " + _col_cnt);

for (int c = 0; c < _col_cnt; c++) {
// Construct the header row
String _h = HeadersLabel[c];
System.out.println("_h: " + _h);

if (_h != null) {
    XSSFCell hd = row.createCell(c);
    hd.setCellValue(_h);                    
}       

 int r = 3;     
for (Iterator iter = Cells.iterator();iter.hasNext();) {
                Object[]  _o = (Object[]) iter.next();
                currentRow = s.createRow(r);
            for(int colNum = 0; colNum < _col_cnt; colNum++){
                XSSFCell currentCell =currentRow.createCell(colNum);       

                if (CellDataType[c].equals("STRING")
                        || CellDataType[c].equals("VARCHAR")) {
                    String _l = (String) _o[colNum];
                    if (_l != null) {
                        currentCell.setCellValue(_l);
                        System.out.println("Data: " + _l);
                    }       
                }

    else if (CellDataType[c].equals("DOUBLE")) {
                    Double _D = (Double) _o[c];
                    if (_D != null) {
                        //XSSFCell cell = rowData.createCell(c);
                        cell.setCellValue(_D);
                    }

                } else if (CellDataType[c].equals("INTEGER")) {
                    Integer _I = (Integer) _o[c];
                    if (_I != null) {
                        //XSSFCell cell = rowData.createCell(c);
                        cell.setCellValue(_I);

                    }
                } else if (CellDataType[c].equals("DATE")) {
                    Date _aDate = (Date) _o[c];
                    if (_aDate != null) {
                        //XSSFCell cell = rowData.createCell(c);
                        cell.setCellValue(_aDate);
                    }
                } else if (CellDataType[c].equals("TIMESTAMP")) {
                    Timestamp _aTimestamp = (Timestamp) _o[c];
                    Date _aDate = Timestamp2Date(_aTimestamp);
                    if (_aDate != null) {
                        //XSSFCell cell = rowData.createCell(c);
                        cell.setCellValue(_aDate);

                    }
                }
        r++;
    }

}

FileOutputStream fos = new FileOutputStream(excelFileName);
//w.write(response.getOutputStream());
w.write(fos);
fos.close();

} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
    try {
        out.close();
    } catch (IOException e1) {
        e1.printStackTrace();
    }
}
}

context.responseComplete();

}

XLSX excel 无法捕获一些数据.当假设有数据出现时，前两列是空的.只有第三列有数据.

The XLSX excel did not manage to capture some data. The first two column is empty when there is suppose to be data appearing. Only the third column has the data.

现在的样子:https://www.dropbox.com/s/2vfxsootyln6qq5/Capture3.JPG 它应该是什么样子:https://www.dropbox.com/s/d0yctgk4pywh140/Capture2.JPG

What it looks like now: https://www.dropbox.com/s/2vfxsootyln6qq5/Capture3.JPG What it suppose to be like: https://www.dropbox.com/s/d0yctgk4pywh140/Capture2.JPG

推荐答案

我不确定数据源...不过我已经尽量解决你的问题了.请随时更改.

I am not sure about the data source... However I have tried to solve your problem As far as possible. Please change it wherever you need.

 try {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename="+ ReportID + ".xlsx");
        String excelFileName = "C:\\Test.xlsx";
        XSSFWorkbook w = new XSSFWorkbook();
        System.out.println("w: " + w);
        XSSFSheet s = w.createSheet(ReportID);
        System.out.println("s: " + s);

        // Report Title
        s.createRow(0).createCell(0).setCellValue(Title);
        System.out.println("Title: " + Title);

        // Populate the worksheet
        int _col_cnt = HeadersLabel.length;
        XSSFRow row = s.createRow(_col_cnt);
        System.out.println("HeadersLabel: " + _col_cnt);

        //For Headers
        int headerRowNum = 2; //for App, ShortName, LongName
        XSSFRow currentRow = s.createRow(headerRowNum);
        for(int headerCol =0; headerCol <_col_cnt; headerCol++){
            currentRow.createCell(headerCol).setCellValue(HeadersLabel[headerCol]);
        }


        // for Date entry 
        for(int dataRow=3;dataRow < 20;dataRow++){
            currentRow = s.createRow(dataRow);
            for(int colNum=0;colNum<_col_cnt;colNum++){
                XSSFCell currentCell =currentRow.createCell(colNum);
                if (CellDataType[c].equals("STRING") || CellDataType[c].equals("VARCHAR")) {
                    String _l = (String) _o[c];
                    if (_l != null) {
                        currentCell.setCellValue(_l);
                    }

                } else if (CellDataType[c].equals("DOUBLE")) {
                    Double _D = (Double) _o[c];
                    if (_D != null) {
                        currentCell.setCellValue(_D);
                    }

                } else if (CellDataType[c].equals("INTEGER")) {
                    Integer _I = (Integer) _o[c];
                    if (_I != null) {
                        currentCell.setCellValue(_I);

                    }
                } else if (CellDataType[c].equals("DATE")) {
                    Date _aDate = (Date) _o[c];
                    if (_aDate != null) {
                        currentCell.setCellValue(_aDate);
                    }
                } else if (CellDataType[c].equals("TIMESTAMP")) {
                    Timestamp _aTimestamp = (Timestamp) _o[c];
                    Date _aDate = Timestamp2Date(_aTimestamp);
                    if (_aDate != null) {
                        currentCell.setCellValue(_aDate);
                    }
                }
            }
        }
    }
}

这篇关于XLSX Apache Java 上的单元格和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！