Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook#close()

@Test
public void lastRowNum2333() throws IOException, InvalidFormatException {
    String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new File(file));
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    Cell cell = xssfSheet.getRow(0).createCell(9);
    cell.setCellValue("testssdf是士大夫否t");

    FileOutputStream fileout = new FileOutputStream("d://test/r2" + System.currentTimeMillis() + ".xlsx");
    sxssfWorkbook.write(fileout);
    sxssfWorkbook.dispose();
    sxssfWorkbook.close();

    xssfWorkbook.close();
}
 

public static Object[] readAllExcelSheet(InputStream inputStream) throws IOException {
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
	int sheets = xssfWorkbook.getNumberOfSheets();
	Object[] objects = new Object[sheets];
	for(int i=0;i<sheets;i++){
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(i);
		objects[i] = readSheet(xssfSheet);
	}
	xssfWorkbook.close();
	return objects;
}
 

public static void writeExcel(HttpServletResponse response,List<String> list) throws Exception {
	response.setContentType("application/vnd.ms-excel");//文件格式,此处设置为excel
	response.setHeader("Content-Disposition","attachment;filename=file.xls");//此处设置了下载文件的默认名称
	ServletOutputStream sos = response.getOutputStream();
    //创建一个新的excel
	XSSFWorkbook wb = new XSSFWorkbook();//XSSFWorkbook
	/**
	 * 采用现成Excel模板
	 * 用这种方式得先保证每个cell有值,不然会报空指针
	 * 有时我们用row.getCell(i)会得到null,那么此时就要用Iterator<Cell> it = row.cellIterator();
	 * XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File("D://a.xlsx")));
	 * XSSFSheet sheet = wb.getSheet("Sheet1");
	 * row[i] = sheet.getRow(i);
	 * headerCell[j] = row[i].getCell(j);
	 */
	//创建sheet页
	XSSFSheet sheet = wb.createSheet("sheet1");//sheet名
	//创建行数
	XSSFRow[] row = new XSSFRow[list.size()];
	//插入数据
	for (int i = 0; i < row.length; i++) {
		row[i] = sheet.createRow(i);
		sheet.setDefaultColumnWidth(30);//设置列的长度
		String info[] = list.get(i).split(",");
		XSSFCell[] headerCell = new XSSFCell[info.length];
		for (int j = 0; j < headerCell.length; j++) {
			headerCell[j] = row[i].createCell(j);
			headerCell[j].setCellValue(new XSSFRichTextString(info[j]));
			/**设置模板样式*/
			//headerCell[j].setCellStyle(setStyle(wb));
		}
	}
	wb.write(sos);
	wb.close();
    sos.flush();
    sos.close();
    response.flushBuffer();
}
 

public static void writeExcelObject(HttpServletResponse response,List<Object> data,String sheetName,String fileName) throws Exception{
	response.setContentType("application/vnd.ms-excel");
	response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
	ServletOutputStream outputStream = response.getOutputStream();
	XSSFWorkbook xssfWorkbook = writeSheetObject(data,sheetName);
	xssfWorkbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
	response.flushBuffer();
	xssfWorkbook.close();
}
 

public static void writeExcelObject(OutputStream outputStream,List<Object> data,String sheetName) throws Exception{
	XSSFWorkbook xssfWorkbook = writeSheetObject(data,sheetName);
	xssfWorkbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
	xssfWorkbook.close();
}
 

public static void writeExcelData(HttpServletResponse response,List<List<String>> data,String sheetName,String fileName) throws IOException{
	response.setContentType("application/vnd.ms-excel");
	response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
	ServletOutputStream outputStream = response.getOutputStream();
	XSSFWorkbook xssfWorkbook = writeSheetData(data,sheetName);
	xssfWorkbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
	response.flushBuffer();
	xssfWorkbook.close();
}
 

public static void writeExcelData(OutputStream outputStream,List<List<String>> data,String sheetName) throws IOException{
	XSSFWorkbook xssfWorkbook = writeSheetData(data,sheetName);
	xssfWorkbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
	xssfWorkbook.close();
}
 

public static List<?> readSingleExcelSheet(InputStream inputStream,int sheetIndex,Map<Integer,String> map,Class<?> objectClass) throws Exception {
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
	List<?> list = readSheet(xssfSheet,map,objectClass);
	xssfWorkbook.close();
	return list;
}
 

public static List<List<String>> readSingleExcelSheet(InputStream inputStream,int sheetIndex) throws IOException {
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
	List<List<String>> list = readSheet(xssfSheet);
	xssfWorkbook.close();
	return list;
}
 

public static List<?> readSingleExcelSheet(InputStream inputStream,String sheetName,Map<Integer,String> map,Class<?> objectClass) throws Exception {
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
	XSSFSheet xssfSheet = xssfWorkbook.getSheet(sheetName);
	List<?> list = readSheet(xssfSheet,map,objectClass);
	xssfWorkbook.close();
	return list;
}
 

public static List<List<String>> readSingleExcelSheet(InputStream inputStream,String sheetName) throws IOException {
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
	XSSFSheet xssfSheet = xssfWorkbook.getSheet(sheetName);
	List<List<String>> list = readSheet(xssfSheet);
	xssfWorkbook.close();
	return list;
}
 

/**
 * poi导出，没用easypoi
 * 不要用swagger测试导出功能，下载的文件异常
 * 直接在浏览器访问http://localhost:8080/swagger-ui.html
 * 火狐浏览器可能出现文件名乱码，谷歌浏览器正常
 * @param response
 * @throws Exception
 */
@ApiOperation("导出excel")
@GetMapping("excel/export")
public void exportExcel(HttpServletResponse response) throws Exception{

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();

    try {
        //表头
        XSSFRow r0 = sheet.createRow(0);
        XSSFCell c00 = r0.createCell(0);
        c00.setCellValue("列1");
        XSSFCell c01 = r0.createCell(1);
        c01.setCellValue("列2");

        //数据
        XSSFRow r1 = sheet.createRow(1);
        XSSFCell c10 = r1.createCell(0);
        c10.setCellValue("数据1");
        XSSFCell c11 = r1.createCell(1);
        c11.setCellValue("数据2");

        response.reset();

        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试.xlsx", "utf-8"));

        //将文件输出
        workbook.write(response.getOutputStream());
    } catch (Exception e) {
        log.error("导出excel错误{}", e.getMessage());
        e.printStackTrace();
    } finally {
        workbook.close();
    }
}
 

public static void writeExcel(HttpServletResponse response,List<String> list) throws Exception {
	response.setContentType("application/vnd.ms-excel");//文件格式,此处设置为excel
	response.setHeader("Content-Disposition","attachment;filename=file.xls");//此处设置了下载文件的默认名称
	ServletOutputStream sos = response.getOutputStream();
    //创建一个新的excel
	XSSFWorkbook wb = new XSSFWorkbook();//XSSFWorkbook
	/**
	 * 采用现成Excel模板
	 * 用这种方式得先保证每个cell有值,不然会报空指针
	 * 有时我们用row.getCell(i)会得到null,那么此时就要用Iterator<Cell> it = row.cellIterator();
	 * XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File("D://a.xlsx")));
	 * XSSFSheet sheet = wb.getSheet("Sheet1");
	 * row[i] = sheet.getRow(i);
	 * headerCell[j] = row[i].getCell(j);
	 */
	//创建sheet页
	XSSFSheet sheet = wb.createSheet("sheet1");//sheet名
	//创建行数
	XSSFRow[] row = new XSSFRow[list.size()];
	//插入数据
	for (int i = 0; i < row.length; i++) {
		row[i] = sheet.createRow(i);
		sheet.setDefaultColumnWidth(30);//设置列的长度
		String info[] = list.get(i).split(",");
		XSSFCell[] headerCell = new XSSFCell[info.length];
		for (int j = 0; j < headerCell.length; j++) {
			headerCell[j] = row[i].createCell(j);
			headerCell[j].setCellValue(new XSSFRichTextString(info[j]));
			/**设置模板样式*/
			//headerCell[j].setCellStyle(setStyle(wb));
		}
	}
	wb.write(sos);
	wb.close();
    sos.flush();
    sos.close();
    response.flushBuffer();
}
 

private XSSFSheet getWSResultForQuery(SolrQueryRequest req, SolrQueryResponse rsp) throws Exception {
  ByteArrayOutputStream xmlBout = new ByteArrayOutputStream();
  writerXlsx.write(xmlBout, req, rsp);
  XSSFWorkbook output = new XSSFWorkbook(new ByteArrayInputStream(xmlBout.toByteArray()));
  XSSFSheet sheet = output.getSheetAt(0);
  req.close();
  output.close();
  return sheet;
}
 

@Test
	public void test1() throws IOException {
		
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		String imgPath = "D:\\Xiaoi\\logo\\logo.png";
		BufferedImage bufferImg = ImageIO.read(new File(imgPath));
        //这里要注意，第二个参数将会决定插入图片形式，如果是一个png的图片，背景透明，但是此处设置为jpg格式将会自动添加黑色背景
        ImageIO.write(bufferImg, "png", byteArrayOut);
        
        
        String filepath = "E:\\ConvertTester\\excel\\abcde.xlsx";
		File originFile = new File(filepath);
		InputStream in = new FileInputStream(originFile);
		XSSFWorkbook workbook = new XSSFWorkbook(in);
		XSSFSheet sheet = workbook.createSheet("testSheet");
         //画图的顶级管理器，一个sheet只能获取一个
        XSSFDrawing drawing = sheet.createDrawingPatriarch(); 
        //anchor主要用于设置图片的属性  
//        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);
        /*
         * 参数定义：
         * 第一个参数是（x轴的开始节点）；
         * 第二个参数是（是y轴的开始节点）；
         * 第三个参数是（是x轴的结束节点）；
         * 第四个参数是（是y轴的结束节点）；
         * 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
         * 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）；
         * 第七个参数是（图片宽度，共多少列）；
         * 第8个参数是（图片高度，共多少行）；
         */
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, Short.MAX_VALUE, Integer.MAX_VALUE, 0, 0, 10, 10);
        anchor.setAnchorType(AnchorType.DONT_MOVE_DO_RESIZE);
        //插入图片
        drawing.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
        workbook.write(new FileOutputStream("E:\\ConvertTester\\excel\\abcd-011.xlsx"));
        workbook.close();
	}
 

@Test
public void lastRowNum255() throws IOException, InvalidFormatException {
    String file = "D:\\test\\complex.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new File(file));
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    xssfSheet.shiftRows(1, 4, 10, true, true);

    FileOutputStream fileout = new FileOutputStream("d://test/r2" + System.currentTimeMillis() + ".xlsx");
    sxssfWorkbook.write(fileout);
    sxssfWorkbook.dispose();
    sxssfWorkbook.close();

    xssfWorkbook.close();
}
 

/**
 * 导出Excel的例子
 * 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
 * @return
 * @throws Exception
 */
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
    logger.trace("exportExcel");
    HttpHeaders responseHeaders = new HttpHeaders();
    responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
    responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);

    //中文文件名需要用iso8859-1编码
    InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
    XSSFSheet sheet = workbook.getSheetAt(0);

    List<SampleItem> list = getDataList();

    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));

    for (int i=0; i<list.size(); i++) {
        SampleItem si = list.get(i);

        XSSFRow row = sheet.createRow(i + 1);

        Cell cell1 = row.createCell(0);
        cell1.setCellValue(si.getDate());
        cell1.setCellStyle(cellStyle);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(si.getName());

        Cell cell3 = row.createCell(2);
        cell3.setCellValue(si.getScore());
    }

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    workbook.close();
    return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    String fileLocation = LondonLonelinessImporter.DatasourceId.lonelinessPrevalence.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xlsx");

    List<TimedValue> timedValues = new ArrayList<TimedValue>();

    XSSFWorkbook workbook = new XSSFWorkbook(isr);

    if (geographyScope == null || geographyScope.isEmpty()) {
        geographyScope = new ArrayList<>();
        geographyScope.add("localAuthority");
        log.warn("No geography scope provided. Defaulting to Local Authority");
    }

    for (String geographyLabel : geographyScope) {
        Sheet datatypeSheet = null;
        SubjectType subjectType = null;
        List<Integer> validAttributes = new ArrayList<>();

        switch (geographyLabel) {
            case "localAuthority":
                datatypeSheet = workbook.getSheetAt(1);
                subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
                validAttributes.addAll(Arrays.asList(0,1,2,3));
                break;
            case "msoa":
                datatypeSheet = workbook.getSheetAt(2);
                subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));
                validAttributes.addAll(Arrays.asList(0,1,2,3,4));
                break;
            case "lsoa":
                datatypeSheet = workbook.getSheetAt(3);
                subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(2));
                validAttributes.addAll(Arrays.asList(0,2,3,4));
                break;
        }
        // Creating the row iterator object
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();
        LocalDateTime timestamp = TimedValueUtils.parseTimestampString("Jun-15");
        log.info("The analysis was made in {} and we persist it as {}", "June 2015", timestamp);
        // Skipping unrelevant rows
        rowIterator.next();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            String geograghy =  String.valueOf(row.getCell(0)).trim();
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(subjectType, geograghy);
            if (subject != null) {
                try {
                    ListIterator<Integer> it = validAttributes.listIterator();
                    while (it.hasNext()) {
                        Double record = row.getCell(it.nextIndex()+2).getNumericCellValue();
                        Attribute attribute = datasource.getTimedValueAttributes().get(it.next());
                        timedValues.add(new TimedValue(
                                subject,
                                attribute,
                                timestamp,
                                record));
                    }
                } catch (IllegalStateException e) {
                    log.warn("Value for subject " + subject.getLabel() + " not found. " +
                            "Defaulting to 0.0. Consider using a BackoffField or ConstantField.");
                    continue;
                }
            }
        }
    }
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 

private byte[] convertExcelToByteArray(XSSFWorkbook workbook) throws IOException {
    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    workbook.write(outByteStream);
    workbook.close();
    return outByteStream.toByteArray();
}
 

/**
 * {@inheritDoc}
 */
@Override
public void generateReport(final ReportDescriptor descriptor,
                           final Map<String, Object> parameters,
                           final Object data,
                           final String lang,
                           final OutputStream outputStream) {

    if (data == null || data instanceof Collection && ((Collection) data).isEmpty()) {
        LOG.debug("No data, no report will be generated");
        return;

    }

    try {

        final XSSFWorkbook workbook = new XSSFWorkbook();
        final XSSFSheet sheet = workbook.createSheet(descriptor.getReportId());

        final XSSFCellStyle headerStyle = workbook.createCellStyle();
        final XSSFFont font = workbook.createFont();
        font.setBold(true);
        headerStyle.setFont(font);

        int rowNum = 0;

        for (Object dataLine : (Collection) data) {

            final Object[] line = (Object[]) dataLine;

            final Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : line) {
                final Cell cell = row.createCell(colNum++);
                if (rowNum == 1) {
                    cell.setCellStyle(headerStyle);
                }
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Number) {
                    cell.setCellValue(((Number) field).doubleValue());
                } else {
                    cell.setCellValue("");
                }
            }
        }

        workbook.write(outputStream);
        workbook.close();

    } catch (Exception exp) {
        LOG.error("Unable to generate report for " + descriptor + " in " + lang, exp);
    }

}