Java Workbook.write方法代碼示例

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
@RequestMapping(value = "/test2.xlsx", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
@ResponseBody
byte[] testDown() throws IOException, InvalidFormatException {
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int i = 0; i < 60000; i++) {
        Row newRow = sheet.createRow(i);
        for (int j = 0; j < 100; j++) {
            newRow.createCell(j).setCellValue("test" + Math.random());
        }
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    workbook.write(os);
    byte[] bytes = os.toByteArray();
    return bytes;
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
/**
 * 導出
 * @param modelName 模型名稱
 * @param excelType excel格式
 * @param response
 * @return
 * @throws IOException
 */
@RequestMapping(value = { "/excel/{modelName}","/excel/{modelName}/{excelType}" }, method = RequestMethod.GET)
public void exportExcel(@PathVariable String modelName,@PathVariable(required = false) String excelType, HttpServletResponse response) throws IOException {
    //默認導出xls格式excel
    if(excelType==null||"".equals(excelType)){
        excelType="XLS";
    }
    List<SysExportModel> models = modelService.queryExportModelByCondition(modelName);
    // 模型不存在 直接結束
    if (models == null || models.size() == 0) {
        return;
    }
    // 獲取導出數據
    SysExportModel model = models.get(0);
    List<List<Object>> exportData = commonService.getAllData(model.getModelName(), model.getEnames(), null);
    List<String> exportHeaderNames = new ArrayList<String>();
    String[] headerNames = model.getCnames().split(",");
    for (String headerName : headerNames) {
        exportHeaderNames.add(headerName);
    }

    response.reset();
    // 文件下載
    response.setContentType("application/vnd.ms-excel");
    String filename = "報表"+modelName+"（"+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+ "）";

    filename = new String(filename.getBytes("gbk"), "iso-8859-1");
    response.setHeader("Content-disposition", "attachment;filename="+ filename + "."+excelType.toLowerCase());
    response.setBufferSize(1024);

    //獲取excel表單
    ExcelGenerater excelGenerater=new ExcelGenerater();
    ExcelGeneraterBean excelGeneraterBean = excelGenerater.create(modelName, exportHeaderNames, exportData);
    Workbook workbook = excelGeneraterBean.getWorkbook();
    //寫入數據 到流
    workbook.write(response.getOutputStream());
    workbook.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
@Test
public void testExcel07() throws IOException{
	List<UserPlus> list = getData(0, 20000);
	Map<String, String> map = new HashMap<String,String>();
	map.put("msg", "用戶信息導出報表");
	map.put("status", "導出成功");
	long start = System.currentTimeMillis();
	Workbook workbook = ExcelExportUtil.exportExcel07(UserPlus.class, list, map, true);
	long end = System.currentTimeMillis();
	System.out.println("耗時：" + (end - start ) + "毫秒");
	FileOutputStream outputStream = new FileOutputStream("D:/test/user.xlsx");
	workbook.write(outputStream);
	if(SXSSFWorkbook.class.equals(workbook.getClass())){
		SXSSFWorkbook wb = (SXSSFWorkbook)workbook;
		wb.dispose();
	}
	outputStream.flush();
	outputStream.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
public static void createWorkBook() throws IOException {
	// ����excel������
	final Workbook wb = new XSSFWorkbook();
	// ����sheet��ҳ��
	final Sheet sheet1 = wb.createSheet("Sheet_1");
	final Sheet sheet2 = wb.createSheet("Sheet_2");

	for (int i = 0; i < 20; i = i + 2) {
		final Row row1 = sheet1.createRow(i);
		final Row row2 = sheet2.createRow(i);

		for (int j = 0; j < 10; j++) {

			row1.createCell(j).setCellValue(j + "new");
			row2.createCell(j).setCellValue(j + "This is a string");

		}
	}
	// ����һ���ļ� ����Ϊworkbooks.xlsx
	final FileOutputStream fileOut = new FileOutputStream("d:\\workbooks.xlsx");
	// �����洴���Ĺ�����������ļ���
	wb.write(fileOut);
	// �ر������
	fileOut.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
/**@throws IOException 
 * @Excel(headRow=2,dataRow=5,sheetName="用戶統計表",sheetSize=65536)
 * 分sheet測試  
 */
@Test
public void separateSheet() throws IOException {
	//獲取10w條數據
	List<UserPlus> list = getData(0, 100000);
	Map<String, String> map = new HashMap<String,String>();
	map.put("msg", "用戶信息導出報表");
	map.put("status", "導出成功");
	long start = System.currentTimeMillis();
	Workbook workbook = ExcelExportUtil.exportExcel03(UserPlus.class, list, map);
	long end = System.currentTimeMillis();
	System.out.println("耗時：" + (end - start ) + "毫秒");
	FileOutputStream outputStream = new FileOutputStream("D:/test/user.xls");
	workbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
/**
 * Creates an excel file to export message data.
 * The excel workbook will have several sheets named after the map key.
 * Each sheet of the file will have the default form:
 * <ul>
 * <li>Line 1 (Header): KEY | de | fr | more iso-language-codes ...</li>
 * <li>Line 2..n (Values): message.key | Deutsch | Francaise | other translation</li>
 * </ul>
 * The header columns are defined by the provided languages in the resources lists.
 * The concrete row and column numbers can be configured to match more requirements like comments.
 * 
 * @param input a list of message information to write to excel
 * @param output if you want to create xls (Excel 97-2003) files, DO NOT use BufferedOutputStream due to library
 *            issue.
 * @param format use constants FORMAT_EXCEL_97 or FORMAT_EXCEL_2007
 * @throws I18nException if a problem occures
 */
public void createExcel(Map<String, List<MessageResourceEntry>> input,
						OutputStream output,
						String format) throws I18nException
{
	Workbook wb = createWorkbook(format);
	for (Entry<String, List<MessageResourceEntry>> entry : input.entrySet())
	{
		createSheet(entry.getValue(), entry.getKey(), wb);
	}
	try
	{
		LOG.info("Write data to output stream");
		wb.write(output);
	}
	catch (IOException e)
	{
		throw new I18nException("Problem writing to stream", e);
	}
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
/**
 * excel 下載
 */
@RequestMapping(value = "downLoad")
public void downLoad(HttpServletRequest request, HttpServletResponse response) throws Exception {

	Workbook wb = null;
	try {
		logger.info(">>>>>>>>ReportViewController.downLoad start>>");

		//=======================================數據
		List<Map<String,Object>> datas = Lists.newArrayList();
		Map<String,Object> data0 = Maps.newHashMap();
		data0.put("date", "2017-01-01");
		data0.put("date1", "2017-01-01");
		Map<String,Object> data1 = Maps.newHashMap();
		data1.put("shoujidai","100");
		data1.put("daxuedai","100");
		data1.put("zirandai","100");
		data0.put("zidonghebishu",data1);
		datas.add(data0);
		//==========================================


		//設置excel模板
		Map<String, Object> templateParams = Maps.newHashMap();
		XLSTransformer transformer = new XLSTransformer();
		wb = transformer.transformXLS(App.class.getResourceAsStream("/xls/excel.xlsx"), templateParams);
		Sheet billInfoSheet = wb.getSheet("sheet1");

		//設置excel展示配置
		ExcelExportSetting excelExportSetting = new ExcelExportSetting();
		List<PoiCell> cellList = Lists.newArrayList();
		//一行數據的第一列
		cellList.add(new ExcelMergeCell("日期","date"));
		cellList.add(new ExcelMergeCell("日期1","date1"));

		//一行數據的第二個列合並單元格的
		ExcelMergeCell excelMergeCell = new ExcelMergeCell("自動電核筆數","zidonghebishu",
				Arrays.asList(new ExcelCell("大學貸","daxuedai"),
						new ExcelCell("手機貸","shoujidai"),
						new ExcelCell("自然貸","zirandai")));
		cellList.add(excelMergeCell);

		excelExportSetting.setHeaderRow(cellList);//設置表頭
		excelExportSetting.setDataList(datas);//設置數據

		//寫入excel
		ExcelPoiHelp.poiWrite(wb, billInfoSheet, excelExportSetting);

		//寫入response
		String outFile = "outputFile.xls";
		response.reset();
		response.addHeader("Content-Disposition", "attachment;filename="+ new String(outFile.getBytes()));
		OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		wb.write(toClient);

	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		wb.close();
	}

}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
@Test
	public void exportExcel() throws FileNotFoundException, Exception {
		File file = new File("D:/test/test/ppp44.xls");
		FileOutputStream outputStream = new FileOutputStream(file);
		List<Person> list = getData();
		Workbook workbook = null;
		long start = System.currentTimeMillis();
		workbook = ExcelExportUtil.exportExcel03(Person.class, list);
//		workbook = ExcelExportUtil.exportExcel(Person.class,list,ExcelFileType.XLS,null,null,true);
//		list = getData();
//		workbook = ExcelExportUtil.exportExcel(UserPlus.class,list,ExcelFileType.XLSX,map,workbook,true);
		long end = System.currentTimeMillis();
		
		System.out.println("耗時：" + (end-start) + "毫秒");		
		System.out.println("****************************");
		workbook.write(outputStream);
		
		if(SXSSFWorkbook.class.equals(workbook.getClass())){
			SXSSFWorkbook wb = (SXSSFWorkbook)workbook;
			wb.dispose();
		}
		outputStream.flush();
		outputStream.close();
		
	}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
public static void evaluateFormulas(String fileName) throws IOException {
  xLogger.fine("Entering evaluateFormulas. fileName: {0}", fileName);
  // Create a InoutStream from the bytes in the cloud storage.
  // Create a template workbook
  // Evaluate the formulas
  // Save the workbook.
  if (fileName == null || fileName.isEmpty()) {
    xLogger.severe("Cannot evaluate formulas in a null or empty file");
    return;
  }

  InputStream is = null;
  OutputStream os = null;
  // Create a workbook from the bytes
  try {
    // Get the template bytes from GCS ( Note: By now the data has been added to the appropriate sheet/s)
    is = _storageUtil.getInputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName);
    if (is == null) {
      xLogger.severe("Failed to create Input stream for {0}", fileName);
      return;
    }
    Workbook
        templateWb =
        WorkbookFactory.create(
            is); // From the bytes downloaded from the google cloud storage, form the Workbook
    if (templateWb != null) {
      CreationHelper createHelper = templateWb.getCreationHelper();
      xLogger.fine("Created createHelper. {0}", createHelper);
      if (createHelper != null) {
        FormulaEvaluator evaluator = createHelper.createFormulaEvaluator();
        xLogger.fine("Created evaluator. {0}", evaluator);
        if (evaluator != null) {
          evaluator.evaluateAll();
          xLogger.fine("After evaluator.evaluateAll");
          templateWb.setForceFormulaRecalculation(
              true); // Added this line because some formula cells were not getting updated even after calling evaluateAll
          xLogger.fine("After templateWb.setForceFormulaRecalculation");
          // Write to file
          xLogger.fine("Now creating baos");
          os =
              _storageUtil
                  .getOutputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName,
                      false);
          xLogger.fine("os: {0}", os);
          templateWb.write(os); // Write the workbook to OutputStream
          xLogger.fine("Wrote templateWb to baos");
        } // end if evaluator != null
      } // end if createHelper != null
    } // end if templateWb != null
  } catch (Exception e) {
    xLogger.severe("{0} while evaluating formulas in the file {1}. Message: {2}",
        e.getClass().getName(), fileName, e.getMessage(), e);
  } finally {
    if (is != null) {
      is.close();
    }
    if (os != null) {
      os.close();
    }
  }
  xLogger.fine("Exiting evaluateFormulas");
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
protected static void cleanupAndSaveExcel97(NPOIFSFileSystem fs, OutputStream outputStream) throws IOException {
    Workbook wb = WorkbookFactory.create(fs);

    if (wb instanceof HSSFWorkbook) {
        HSSFWorkbook hwb = (HSSFWorkbook) wb;
        InternalWorkbook internal = hwb.getInternalWorkbook();
        if (internal != null) {
            LOGGER.trace("# of Records: {}", internal.getNumRecords());
            removeObProjRecord(internal.getRecords());
            LOGGER.trace("# of Records: {}", internal.getNumRecords());
        }
    }

    wb.write(outputStream);
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
@Override
public boolean write(Workbook workbook, String path) {
	try {
		FileOutputStream out = new FileOutputStream(new File(path));
		workbook.write(out);
		out.close();
		workbook.close();
	} catch (Exception e) {
		return false;
	}
	return true;
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
public static ByteArrayOutputStream build(Long examId, Collection<Long> childIds) throws IOException {

        List<ExamRecord> examRecords = Ebean.find(ExamRecord.class)
                .fetch("examScore")
                .where()
                .eq("exam.parent.id", examId)
                .in("exam.id", childIds)
                .findList();
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Exam records");
        String[] headers = ExamScore.getHeaders();
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        for (ExamRecord record : examRecords) {
            String[] data = record.getExamScore().asArray(record.getStudent(), record.getTeacher(), record.getExam());
            Row dataRow = sheet.createRow(examRecords.indexOf(record) + 1);
            for (int i = 0; i < data.length; ++i) {
                dataRow.createCell(i).setCellValue(data[i]);
            }
        }
        IntStream.range(0, headers.length).forEach(i -> sheet.autoSizeColumn(i, true));
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        bos.close();
        return bos;
    }
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
ExcelTestHelper(final String parent, boolean generateXls) throws Exception {
  this.xls = generateXls;

  // Create a test Excel sheet with all types of supported data
  Workbook wb = generateXls ? new HSSFWorkbook() : new XSSFWorkbook();

  CreationHelper creationHelper = wb.getCreationHelper();
  DataFormat dataFormat = creationHelper.createDataFormat();
  short fmt = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss");
  CellStyle style = wb.createCellStyle();
  style.setDataFormat(fmt);

  Sheet sheetWithHeader = wb.createSheet("Sheet 1");

  // Create header row
  Row headerRow = sheetWithHeader.createRow((short) 0);
  headerRow.createCell(0).setCellValue("Number");
  headerRow.createCell(1).setCellValue("String1");
  headerRow.createCell(2).setCellValue("String2");
  headerRow.createCell(3).setCellValue("MyTime");
  headerRow.createCell(4).setCellValue("Formula");
  headerRow.createCell(5).setCellValue("Boolean");
  headerRow.createCell(6).setCellValue("Error");
  generateSheetData(sheetWithHeader, style, (short)1);

  Sheet sheetWithoutHeader = wb.createSheet("Sheet 2");
  generateSheetData(sheetWithoutHeader, style, (short)0);

  testFilePath = new File(parent, "excelTestFile").getPath();

  // Write the output to a file
  FileOutputStream fileOut = new FileOutputStream(testFilePath);
  wb.write(fileOut);
  fileOut.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
public void writeFile(Workbook workbook, String fileLocation) throws FileNotFoundException, IOException {
	FileOutputStream out = new FileOutputStream(new File(fileLocation));
	try {
		workbook.write(out);
	} finally {
		if (out != null) {
			out.close();
		}
	}
}
 

import org.apache.poi.ss.usermodel.Workbook; //導入方法依賴的package包/類
public void writeOutputStream(Workbook workbook, OutputStream out) throws IOException {
	workbook.write(out);
}