Java Code Examples for org.apache.poi.hssf.usermodel.HSSFWorkbook#write()

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFWorkbook#write() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: BoundaryPointXlsParserTest.java From powsybl-core with Mozilla Public License 2.0

6 votes

@Test
public void test() throws IOException {
    HSSFWorkbook workbook = createWorkbook();

    byte[] buffer;
    try (ByteArrayOutputStream stream = new ByteArrayOutputStream(1024)) {
        workbook.write(stream);
        stream.flush();
        buffer = stream.toByteArray();
    }

    Map<String, BoundaryPoint> boundaryPoints;
    try (InputStream stream = new ByteArrayInputStream(buffer)) {
        BoundaryPointXlsParser parser = new BoundaryPointXlsParser();
        boundaryPoints = parser.parse(stream);
    }

    assertEquals(1, boundaryPoints.size());
    BoundaryPoint point = boundaryPoints.get("BoundaryPoint FR-BE");
    assertNotNull(point);
    assertEquals("BoundaryPoint FR-BE", point.getName());
    assertEquals(Country.FR, point.getBorderFrom());
    assertEquals(Country.BE, point.getBorderTo());
}

Example 2

Source File: TestExportExcel.java From poi with Apache License 2.0

6 votes

@Test
public void exportExcelWithStyle() {
	try {
		String filePath = TestUtil.DOC_PATH + File.separator
				+ Globals.EXPORT_PRODUCT;
		OutputStream os = new FileOutputStream(filePath);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		HSSFRichTextString richString = new HSSFRichTextString(
				TestUtil.RICH_TEXT_STRING);
		HSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		richString.applyFont(font);
		sheet.createRow(0).createCell(0).setCellValue(richString);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}

Example 3

Source File: ProductController.java From springboot-learn with MIT License

6 votes

@RequestMapping("/exportProductsToExcel")
    public void exportProductsToExcel(HttpServletResponse response) {
        try {
            List<Product> products = productService.getProductList(null);
            HSSFWorkbook workbook = productService.exportProductsToExcel(products);

            String filename = "export_products_" + DateUtil.getTime(new Date()).toString().replace(" ", "");
            filename = filename.replace("-", "");
            filename = filename.replace(":", "");

            response.setContentType("application/vnd.ms-excel");
//            response.setHeader("Content-disposition", "attachment;filename="
//                    + filename + ".xls");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + filename + ".xls\"");

            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Example 4

Source File: ExcelPoiTest.java From frpMgr with MIT License

5 votes

public static void main(String[] args) throws Exception {
	File file = new File("e:\\2016年调查表1.xls");
	HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
	HSSFSheet sheet = wb.getSheetAt(2);
	sheet.getRow(5).getCell(3).setCellValue("山东有限公司");
	sheet.getRow(5).getCell(7).setCellValue("3799991911");
	sheet.getRow(8).getCell(3).setCellValue("174");
	sheet.getRow(8).getCell(7).setCellValue("私营股份有限公司");
	wb.write(new FileOutputStream("e:\\2016年调查表2.xls"));
	wb.close();
	System.out.println("success");
}

Example 5

Source File: ReportStudentsUTLCandidatesDA.java From fenixedu-academic with GNU Lesser General Public License v3.0

5 votes

public ActionForward exportErrors(final ActionMapping mapping, final ActionForm actionForm, final HttpServletRequest request,
        HttpServletResponse response) throws IOException {
    ReportStudentsUTLCandidates report = getRenderedObject("report");

    HSSFWorkbook generateReport = report.generateErrors();

    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=erros_bolsa_accao_social.xls");
    generateReport.write(response.getOutputStream());

    response.getOutputStream().flush();
    response.flushBuffer();

    return null;
}

Example 6

Source File: ReportStudentsUTLCandidatesDA.java From fenixedu-academic with GNU Lesser General Public License v3.0

5 votes

public ActionForward exportReport(final ActionMapping mapping, final ActionForm actionForm, final HttpServletRequest request,
        HttpServletResponse response) throws IOException {
    ReportStudentsUTLCandidates report = getRenderedObject("report");

    HSSFWorkbook generateReport = report.generateReport();

    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=bolsa_accao_social_utl.xls");
    generateReport.write(response.getOutputStream());

    response.getOutputStream().flush();
    response.flushBuffer();

    return null;
}

Example 7

Source File: PhdIndividualProgramProcessDA.java From fenixedu-academic with GNU Lesser General Public License v3.0

5 votes

public ActionForward generateReport(ActionMapping mapping, ActionForm form, HttpServletRequest request,
        HttpServletResponse response) throws IOException {
    SearchPhdIndividualProgramProcessBean searchBean =
            (SearchPhdIndividualProgramProcessBean) getObjectFromViewState("searchProcessBean");

    HSSFWorkbook workbook = new HSSFWorkbook();
    PhdIndividualProgramProcessesReport report = new PhdIndividualProgramProcessesReport(workbook);
    report.build(searchBean);

    PhdGuidersReport guidersReport = new PhdGuidersReport(workbook);
    guidersReport.build(searchBean);

    EPFLCandidatesReport epflReport = new EPFLCandidatesReport(workbook);
    epflReport.build(searchBean);

    RecommendationLetterReport recommendationLetterReport = new RecommendationLetterReport(workbook);
    recommendationLetterReport.build(searchBean);

    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=phd.xls");
    workbook.write(response.getOutputStream());

    response.getOutputStream().flush();
    response.flushBuffer();

    return null;
}

Example 8

Source File: ExcelReplaceUtil.java From poi with Apache License 2.0

5 votes

/**
 * 替换Excel模板文件内容
 *
 * @param datas
 *            文档数据
 * @param sourceFilePath
 *            Excel模板文件路径
 * @param targetFilePath
 *            Excel生成文件路径
 */
public static boolean replaceModel(List<ExcelReplaceDataVO> datas,
		String sourceFilePath, String targetFilePath) {
	boolean bool = true;
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				sourceFilePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		for (ExcelReplaceDataVO data : datas) {
			// 获取单元格内容
			HSSFRow row = sheet.getRow(data.getRow());
			HSSFCell cell = row.getCell((short) data.getColumn());
			String str = cell.getStringCellValue();
			// 替换单元格内容
			str = str.replace(data.getKey(), data.getValue());
			// 写入单元格内容
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(str);
		}
		// 输出文件
		FileOutputStream fileOut = new FileOutputStream(targetFilePath);
		wb.write(fileOut);
		fileOut.close();
	} catch (Exception e) {
		bool = false;
		e.printStackTrace();
	}
	return bool;
}

Example 9

Source File: SecurityUtil.java From SQLiteToExcel with Apache License 2.0

5 votes

/**
 * Encrypt a file, support .xls file only
 *
 * @param file
 * @param encryptKey
 * @throws Exception
 */
public static void EncryptFile(File file, String encryptKey) throws Exception {
    FileInputStream fileInput = new FileInputStream(file.getPath());
    BufferedInputStream bufferInput = new BufferedInputStream(fileInput);
    POIFSFileSystem poiFileSystem = new POIFSFileSystem(bufferInput);
    Biff8EncryptionKey.setCurrentUserPassword(encryptKey);
    HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true);
    FileOutputStream fileOut = new FileOutputStream(file.getPath());
    workbook.writeProtectWorkbook(Biff8EncryptionKey.getCurrentUserPassword(), "");
    workbook.write(fileOut);
    bufferInput.close();
    fileOut.close();
}

Example 10

Source File: ExcelUtil.java From util with Apache License 2.0

5 votes

/**
 * 在输出流中导出excel。
 * @param excelName 导出的excel名称 包括扩展名
 * @param sheetName 导出的sheet名称
 * @param fieldName 列名数组
 * @param data 数据组
 * @param response response
 * @throws IOException  转换流时IO错误
 */
public void makeStreamExcel(String excelName, String sheetName,String[] fieldName
		, List<Object[]> data,HttpServletResponse response) throws IOException {
	 OutputStream os = null;
	response.reset(); // 清空输出流
	os = response.getOutputStream(); // 取得输出流
	response.setHeader("Content-disposition", "attachment; filename="
			+ new String(excelName.getBytes(), "ISO-8859-1")); // 设定输出文件头
	response.setContentType("application/msexcel"); // 定义输出类型
	//在内存中生成工作薄
	HSSFWorkbook workbook = makeWorkBook(sheetName,fieldName,data);
	os.flush();
	workbook.write(os);
}

Example 11

Source File: ExcelUtil.java From util with Apache License 2.0

5 votes

/**
 * 在磁盘生成一个含有内容的excel,路径为path属性
 * @param sheetName 导出的sheet名称
 * @param fieldName 列名数组
 * @param data 数据组
 * @throws IOException 
 */
public void makeExcel(String sheetName,String[] fieldName,List<Object[]> data) throws IOException {
	//在内存中生成工作薄
	HSSFWorkbook workbook = makeWorkBook(sheetName,fieldName,data);
	//截取文件夹路径
	String filePath=path.substring(0,path.lastIndexOf("\\"));
	// 如果路径不存在，创建路径
	File file = new File(filePath);
	//System.out.println(path+"-----------"+file.exists());
	if (!file.exists())
		file.mkdirs();
	FileOutputStream fileOut = new FileOutputStream(path);
	workbook.write(fileOut);
	fileOut.close();
}

Example 12

Source File: ExcelFileUtils.java From SWET with MIT License

5 votes

public static void writeXLSFile() throws Exception {

		HSSFWorkbook hddfwb = new HSSFWorkbook();
		HSSFSheet sheet = hddfwb.createSheet(sheetName);

		for (int row = 0; row < tableData.size(); row++) {
			HSSFRow hssfrow = sheet.createRow(row);
			rowData = tableData.get(row);
			for (int col = 0; col < rowData.size(); col++) {
				HSSFCell hssfcell = hssfrow.createCell(col);
				hssfcell.setCellValue(rowData.get(col));
			}
		}

		try (OutputStream fileOutputStream = new FileOutputStream(
				excelFileName)) {
			hddfwb.write(fileOutputStream);
			hddfwb.close();
			fileOutputStream.flush();
			fileOutputStream.close();
		} catch (IOException e) {
			String message = String.format("Exception saving XLS file %s\n",
					excelFileName) + e.getMessage();
			logger.info(message);
			// NOTE: throw exceptions with user friendly messages to be rendered
			// by the master app
			throw new Exception(message);
		}
	}

Example 13

Source File: PoiUtil.java From dk-fitting with Apache License 2.0

5 votes

/**
 * 导出函数有三个参数，主内容数据，标题数组，到处文件名
 */
public void createxls(Object[][] messages, String[] titles, String filename) {
    try {
        FileOutputStream fos = new FileOutputStream( new File( filename ) );
        HSSFWorkbook book = new HSSFWorkbook();// 所有execl的父节点
        HSSFSheet sheet = book.createSheet( "sheet1" );//此处可以随意设置
        HSSFRow hssfrow = sheet.createRow( 0 );//创建首行标题
        HSSFCell hssfcell = null;
        for (int i = 0; i < titles.length; i++) {//创建标题栏目，也就是表格第一行
            hssfcell = hssfrow.createCell( i );
            hssfcell.setCellType( HSSFCell.ENCODING_UTF_16 );
            hssfcell.setCellValue( titles[i] );
        }

        System.out.println( "message:" + messages.length );
        for (int i = 0; i < messages.length; i++) {//添加表格中的内容
            hssfrow = sheet.createRow( i + 1 );//创建表格第二行，由于标记为0,这里设置为一，主要为了区别标题和内容
            Object[] obj = messages[i];
            for (int j = 0; j < obj.length; j++) {
                hssfcell = hssfrow.createCell( j );
                hssfcell.setCellType( HSSFCell.ENCODING_UTF_16 );//关于数据编码的问题
                hssfcell.setCellValue( obj[j] + "" );//转换为字符串的方式
                System.out.print( obj[j] + "\t" );
            }
            System.out.println();
        }
        book.write( fos );
        fos.flush();
        fos.close();
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println( e );
    }
}

Example 14

Source File: ExcelUtils.java From job with MIT License

4 votes

public static void outputResumeProcessResult(Map<File, Resume> map, File outFile) throws IOException {
  //工作簿
  HSSFWorkbook excel = new HSSFWorkbook();
  FileOutputStream fileoutputstream = new FileOutputStream(outFile);
  
  try {
    HSSFSheet sheet = excel.createSheet("简历处理结果");
    
    // 写表头
    HSSFRow headerRow = sheet.createRow(0);
    for(int i = 0; i < HEADERS.length; i++) {
      headerRow.createCell(i).setCellValue(HEADERS[i]);
    }
    
    // 写内容
    int rowNum = 1;
    for(Map.Entry<File, Resume> entry : map.entrySet()) {
      Resume resume = entry.getValue();
      HSSFRow row = sheet.createRow(rowNum);
      row.createCell(0).setCellValue(rowNum);                    // 序号
      row.createCell(1).setCellValue(resume.getName());          // 姓名
      row.createCell(2).setCellValue(resume.getJob());          // 应聘职位
      row.createCell(3).setCellValue(resume.getSource());        // 简历来源
      row.createCell(4).setCellValue(resume.getSchool());        // 毕业院校
      row.createCell(5).setCellValue(resume.getEducation());    // 学历
      row.createCell(6).setCellValue(resume.getAge());           // 年龄
      row.createCell(7).setCellValue(resume.getWorkDuration()); // 工作经验
      row.createCell(8).setCellValue(resume.getCompany());      // 现工作单位
      row.createCell(9).setCellValue(resume.getPhone());         // 电话
      row.createCell(10).setCellValue(resume.getMail());          // 邮箱
      row.createCell(11).setCellValue(resume.getSex());           // 性别
      row.createCell(12).setCellValue(resume.getBirthday());      // 生日
      row.createCell(13).setCellValue(resume.getCity());          // 居住城市
      row.createCell(14).setCellValue(resume.getAddress());       // 地址
      rowNum++;
    }
    excel.write(fileoutputstream);
  } finally {
    fileoutputstream.close();
    excel.close();
  }
}

Example 15

Source File: MailTests.java From spring-boot-101 with Apache License 2.0

4 votes

@Test
public void sendMailWithExcel() throws IOException {
	String[] headers = {"col1","col2","col3"};
	// 声明一个工作薄
	HSSFWorkbook wb = new HSSFWorkbook();
	// 生成一个表格
	HSSFSheet sheet = wb.createSheet();
	HSSFRow row = sheet.createRow(0);
	for (int i = 0; i < headers.length; i++) {
		HSSFCell cell = row.createCell(i);
		cell.setCellValue(headers[i]);
	}
	int rowIndex = 1;

	for(int j=0; j<3; j++){
		row = sheet.createRow(rowIndex);
		rowIndex++;
		HSSFCell cell1 = row.createCell(0);
		cell1.setCellValue(j);
		cell1 = row.createCell(1);
		cell1.setCellValue(j+1);
		cell1 = row.createCell(2);
		cell1.setCellValue(j+2);
	}
	for (int i = 0; i < headers.length; i++) {
		sheet.autoSizeColumn(i);
	}

	ByteArrayOutputStream os = new ByteArrayOutputStream(1000);
	wb.write(os);
	wb.close();

	InputStreamSource iss = new ByteArrayResource(os.toByteArray());
	os.close();

	mailService.sendAttachmentsMail("[email protected]",
			"attachmentMail subject",
			"I have an attachment",
			iss, "abc1.xlsx");

}

Example 16

Source File: ExportEventsImpl.java From neoscada with Eclipse Public License 1.0

4 votes

private IStatus storeExcel ( final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor ) throws IOException
{
    final HSSFWorkbook workbook = new HSSFWorkbook ();

    final HSSFDataFormat dateFormat = workbook.createDataFormat ();
    final HSSFCellStyle dateCellStyle = workbook.createCellStyle ();
    dateCellStyle.setDataFormat ( dateFormat.getFormat ( "YYYY-MM-DD hh:mm:ss.000" ) );

    try
    {
        monitor.beginTask ( Messages.ExportImpl_Progress_ExportingEvents, events.size () + 3 + columns.size () );

        try
        {
            monitor.subTask ( Messages.ExportImpl_Progress_CreateWorkbook );
            monitor.worked ( 1 );

            final HSSFSheet sheet = createSheet ( events, workbook, columns );
            monitor.worked ( 1 );

            monitor.setTaskName ( Messages.ExportImpl_Progress_ExportEvents );

            for ( int i = 0; i < events.size (); i++ )
            {
                final HSSFRow row = sheet.createRow ( i + 1 );

                final Event e = events.get ( i );
                for ( int j = 0; j < columns.size (); j++ )
                {
                    final Field field = columns.get ( j );
                    final ExcelCell cell = new ExcelCell ( row, j, dateCellStyle );
                    field.render ( e, cell );
                }
                monitor.worked ( 1 );
                if ( monitor.isCanceled () )
                {
                    return Status.CANCEL_STATUS;
                }
            }

            sheet.setRepeatingRows ( new CellRangeAddress ( 0, 1, -1, -1 ) );

            monitor.setTaskName ( "Auto sizing" );
            for ( int i = 0; i < columns.size (); i++ )
            {
                monitor.subTask ( String.format ( "Auto sizing column: %s", columns.get ( i ).getHeader () ) );
                sheet.autoSizeColumn ( i );
                monitor.worked ( 1 );

                if ( monitor.isCanceled () )
                {
                    return Status.CANCEL_STATUS;
                }
            }

        }
        finally
        {
            monitor.subTask ( Messages.ExportImpl_Progress_CloseFile );
            if ( workbook != null )
            {
                makeDocInfo ( workbook );

                final FileOutputStream stream = new FileOutputStream ( file );
                workbook.write ( stream );
                stream.close ();
            }
            monitor.worked ( 1 );
        }
    }
    finally
    {
        monitor.done ();
    }

    return Status.OK_STATUS;
}

Example 17

Source File: AgentManageServiceImpl.java From DrivingAgency with MIT License

4 votes

private String derivedStudentInfo(List<StudentVo> studentVos){

        String agentEmail = SecurityContextHolder.getAgent().getAgentEmail();
        if (StringUtils.isBlank(agentEmail)){
            throw new ParamException("你的邮箱为空,不能导出Excel数据");
        }

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("获取学员信息Excel表格");
        HSSFRow row = null;
        row = sheet.createRow(0);
        row.setHeight((short) (26.25 * 20));
        row.createCell(0).setCellValue("学员信息列表");
        CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 8);
        sheet.addMergedRegion(rowRegion);
        row = sheet.createRow(1);
        row.setHeight((short) (22.50 * 20));//设置行高
        row.createCell(0).setCellValue("学员Id");//为第一个单元格设值
        row.createCell(1).setCellValue("学院学号");//为第一个单元格设值
        row.createCell(2).setCellValue("学员姓名");//为第二个单元格设值
        row.createCell(3).setCellValue("学员手机号");//为第三个单元格设值
        row.createCell(4).setCellValue("学员身份证照片地址");//为第四个单元格设值
        row.createCell(5).setCellValue("学员学费");//为第五个单元格设值
        row.createCell(6).setCellValue("学员学校");//为第六个单元格设值
        row.createCell(7).setCellValue("学员添加者");//为第七个单元格设值
        row.createCell(8).setCellValue("学员");//为第八个单元格设值

        for (int i = 0; i < studentVos.size(); i++) {
            row = sheet.createRow(i + 2);
            StudentVo studentVo = studentVos.get(i);
            row.createCell(0).setCellValue(studentVo.getId());
            row.createCell(1).setCellValue(studentVo.getStudentId());
            row.createCell(2).setCellValue(studentVo.getStudentName());
            row.createCell(3).setCellValue(studentVo.getStudentPhone());
            row.createCell(4).setCellValue(studentVo.getStudentImg());
            if (studentVo.getStudentPrice()==null){
                row.createCell(5).setCellValue(0);
            }else{
                row.createCell(5).setCellValue(studentVo.getStudentPrice().doubleValue());
            }
            row.createCell(6).setCellValue(studentVo.getStudentSchool());
            row.createCell(7).setCellValue(studentVo.getOperator());
            row.createCell(8).setCellValue(DateTimeUtil.dateToStr(studentVo.getUpdateTime()));
        }
        sheet.setDefaultRowHeight((short) (16.5 * 20));
        for (int i = 0; i <= 13; i++) {
            sheet.autoSizeColumn(i);
        }
        try {

            String folder=System.getProperty("java.io.tmpdir");
            File file=new File(folder,UUID.randomUUID().toString()+".xls");
            if (!file.exists()){
                file.createNewFile();
            }
            wb.write(file);

            mailSenderUtil.sendAttachmentMail(agentEmail,"【驾校全部学员数据】",
                    "驾校代理小程序中的全部学员数据,Excel在附件中",file);

            return "Excel导出成功";
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }