POI导出Excel再导入解析不到数据

0 0

POI导出Excel再导入解析不到数据5

POI导出Excel,打开没问题，直接导入解析不到数据,如果用Excel软件随便编辑一下导入可以解析到数据,导出大致如下　

SXSSFWorkbook wb = dynamicTableService.getDTExcelBySql(sql, selectColumnList, showColumnMap);
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();

/**
* 导出excel文件
*
* @param sql
* @param titleList
* 表格表头,将titleList 中的对像转为string
* @return
*/
@SuppressWarnings({ "unused", "unchecked" })
public SXSSFWorkbook getDTExcelBySql(String sql, List<DynamicTableValue> titleList, HashMap<Long, String> showColumnMap) {
long start = System.currentTimeMillis();
int pn = 1;
int totalCount = getCountBySql(sql);
int pageSize = Config.EXPORT_EXCEL_SIZE;// 一次查询条数据
int pageCount = 0;// 页数
if (totalCount % pageSize == 0) {
pageCount = totalCount / pageSize;
} else {
pageCount = totalCount / pageSize + 1;
}
List<Object[]> dataList = getPageBySQL(sql, pn, pageSize);
int currentRowNum = 0;
SXSSFWorkbook wb = new SXSSFWorkbook(500);// 设置内存中缓存记录数
Sheet sheet = wb.createSheet("sheet1");
CellStyle style = wb.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式
if (titleList != null && titleList.size() > 0) {
Row row = sheet.getRow(currentRowNum) == null ? sheet.createRow(currentRowNum) : sheet.getRow(currentRowNum);
currentRowNum++;
CellStyle rowStyle = wb.createCellStyle();
rowStyle.setFillForegroundColor(HSSFColor.LIME.index);//背景颜色
// rowStyle.setFillBackgroundColor(HSSFColor.LIME.index);//
rowStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
rowStyle.setAlignment(CellStyle.ALIGN_CENTER);//居中
// rowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
// rowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// rowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
// rowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

Font font = wb.createFont();
font.setFontName("宋体");//字体
font.setFontHeightInPoints((short) 12);//字体大小
rowStyle.setFont(font);
row.setRowStyle(rowStyle);
int excelColIndex = 0;// 记录真实的显示的列位置,因有字段不显示，所以不能用colIndex
for (int colIndex = 0; colIndex < titleList.size(); colIndex++) {
DynamicTableValue dtv = titleList.get(colIndex);
if (showColumnMap.get(dtv.getId()) != null || "true".equals(dtv.getPrimaryKey())) {
Cell cell = row.createCell(excelColIndex);
cell.setCellValue(dtv.getColumnNameCN());
cell.setCellStyle(rowStyle);
excelColIndex++;
}
}
}

CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
CellStyle rowStyle = wb.createCellStyle();
rowStyle.setFillForegroundColor(HSSFColor.LIME.index);

while (dataList != null && dataList.size() > 0) {
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.getRow(currentRowNum) == null ? sheet.createRow(currentRowNum) : sheet.getRow(currentRowNum);
currentRowNum++;
row.setRowStyle(rowStyle);
Object[] f = dataList.get(i);
int excelColIndex = 0;// 记录真实的显示的列位置,因有字段不显示，所以不能用colIndex
for (int colIndex = 0; colIndex < f.length; colIndex++) {
DynamicTableValue dtv = titleList.get(colIndex);
if (showColumnMap.get(dtv.getId()) != null || "true".equals(dtv.getPrimaryKey())) {
Cell cell = row.createCell(excelColIndex);
cell.setCellValue(f[colIndex] == null ? "" : f[colIndex].toString());
cell.setCellStyle(cellStyle);
excelColIndex++;
}
}
}
pn++;
dataList = getPageBySQL(sql, pn, pageSize);
}
long used = (System.currentTimeMillis() - start);
// System.out.println(used);
return wb;
}

问题补充：读取Excel方法:

public class XxlsPrint extends XxlsAbstract {
private static int totalCount=0;
    @Override
    public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
        for (int i = 0; i < rowlist.size(); i++) {
            System.out.print("'" + rowlist.get(i) + "',");
        }
        System.out.println();
        totalCount++;
    }

    public static void main(String[] args) throws Exception {
        XxlsPrint howto = new XxlsPrint();
        long start = System.currentTimeMillis();
//        howto.processOneSheet("e:\\log.xlsx",1);
//        howto.processOneSheet("e:\\dt_demo_20130522113626.xlsx",1);
//        howto.process("e:\\dt_demo_20130522113626.xlsx");
        howto.process("e:\\dt_demo_2.xlsx");

        long used = (System.currentTimeMillis() - start);
        System.out.println("文件中总数据条数:"+totalCount+",循环用时:"+used+" 注:不输出信息时所用时间");
//      howto.processAllSheets("F:/new.xlsx");
    }
}

/**
* 遍历 excel 文件
*/
public void process(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
int counterIndex=0;
while (sheets.hasNext()) {
counterIndex++;
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
System.out.println(counterIndex);
pkg.close();
}