Java Code Examples for org.apache.poi.xssf.usermodel.XSSFSheet

The following examples show how to use org.apache.poi.xssf.usermodel.XSSFSheet. These examples are extracted from open source projects. 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
private MetaFile writeExcel(Map<String, List<String[]>> data) throws IOException {

    XSSFWorkbook workBook = new XSSFWorkbook();

    for (String model : data.keySet()) {
      XSSFSheet sheet = workBook.createSheet(model);
      int count = 0;
      for (String[] record : data.get(model)) {
        XSSFRow row = sheet.createRow(count);
        int cellCount = 0;
        for (String val : record) {
          XSSFCell cell = row.createCell(cellCount);
          cell.setCellValue(val);
          cellCount++;
        }
        count++;
      }
    }

    File excelFile = MetaFiles.createTempFile("Data", ".xls").toFile();
    FileOutputStream out = new FileOutputStream(excelFile);
    workBook.write(out);
    out.close();

    return metaFiles.upload(excelFile);
  }
 
Example #2
Source Project: mzmine3   Author: mzmine   File: XSSFExcelWriterReader.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * Writes the data to a cell and returns the cell.
 *
 * @param sheet the sheet to write on
 * @param icol index of column
 * @param irow index of row
 * @param data data to write in cell (String, Date, Boolean, Double, Float, Integer, Number)
 * @return the given cell at icol;irow
 * @see
 */
public Cell writeToCell(XSSFSheet sheet, int icol, int irow, Object data) {
  Cell cell = getCell(sheet, icol, irow);
  // write data
  if (data instanceof Date)
    cell.setCellValue((Date) data);
  else if (data instanceof Boolean)
    cell.setCellValue((Boolean) data);
  else if (data instanceof String)
    cell.setCellValue((String) data);
  else if ((data instanceof Double))
    cell.setCellValue((Double) data);
  else if ((data instanceof Number))
    cell.setCellValue(((Number) data).doubleValue());
  else if ((data instanceof Integer))
    cell.setCellValue(((Integer) data).doubleValue());
  else if ((data instanceof Float))
    cell.setCellValue(((Float) data).doubleValue());
  return cell;
}
 
Example #3
Source Project: mzmine3   Author: mzmine   File: XSSFExcelWriterReader.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * exports a data[rows][columns] array
 *
 * @param realFilePath
 * @param data
 * @param rowsFirst true: [rows][cols] false [cols][rows]
 */
public XSSFWorkbook exportDataArrayToFile(File file, String sheetname, Object[][] data,
    boolean rowsFirst) {
  // open wb
  XSSFWorkbook wb = new XSSFWorkbook();
  XSSFSheet sheet = getSheet(wb, sheetname);
  // write to wb
  for (int r = 0; r < data.length; r++) {
    // all columns
    for (int c = 0; c < data[r].length; c++) {
      if (data[r][c] != null) {
        if (rowsFirst)
          writeToCell(sheet, c, r, data[r][c]);
        else
          writeToCell(sheet, r, c, data[r][c]);
      }
    }
  }

  // save wb
  saveWbToFile(file, wb);
  return wb;
}
 
Example #4
Source Project: bamboobsc   Author: billchen198318   File: KpiPeriodTrendsExcelCommand.java    License: Apache License 2.0 6 votes vote down vote up
private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putTables(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "kpis-period-trends.xlsx");
	file = null;
	return oid;
}
 
Example #5
Source Project: bamboobsc   Author: billchen198318   File: PdcaReportExcelCommand.java    License: Apache License 2.0 6 votes vote down vote up
private String createExcel(Context context) throws Exception {
	
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	int row = 0;
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh1 = wb.createSheet( "PDCA Report" );
	
	row += this.createPdca(wb, sh1, row, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "pdca-report.xlsx");
	file = null;
	return oid;
}
 
Example #6
Source Project: autopoi   Author: zhangdaiscott   File: PoiPublicUtil.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 获取Excel2007图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) {
	Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
	for (POIXMLDocumentPart dr : sheet.getRelations()) {
		if (dr instanceof XSSFDrawing) {
			XSSFDrawing drawing = (XSSFDrawing) dr;
			List<XSSFShape> shapes = drawing.getShapes();
			for (XSSFShape shape : shapes) {
				XSSFPicture pic = (XSSFPicture) shape;
				XSSFClientAnchor anchor = pic.getPreferredSize();
				CTMarker ctMarker = anchor.getFrom();
				String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
				sheetIndexPicMap.put(picIndex, pic.getPictureData());
			}
		}
	}
	return sheetIndexPicMap;
}
 
Example #7
Source Project: mzmine2   Author: mzmine   File: XSSFExcelWriterReader.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * writes regression data to sheets
 * 
 * @param sheet
 * @param reg
 * @param dp
 * @param quantifier
 */
public void writeRegressionToSheet(XSSFSheet sheet, SimpleRegression reg, double[][] dp) {
  //
  writeToCell(sheet, 0, 1, "c = (I-intercept)/slope");
  writeToCell(sheet, 0, 2, "intercept = ");
  writeToCell(sheet, 1, 2, reg.getIntercept());
  writeToCell(sheet, 0, 2, "slope = ");
  writeToCell(sheet, 1, 2, reg.getSlope());
  writeToCell(sheet, 0, 3, "R^2 = ");
  writeToCell(sheet, 1, 3, reg.getRSquare());
  writeToCell(sheet, 0, 3, "R^2 = ");
  // datapoints
  writeToCell(sheet, 4, 0, "regression");
  writeToCell(sheet, 4, 1, "x");
  writeToCell(sheet, 5, 1, "intensity");
  writeDataArrayToSheet(sheet, dp, 4, 2, true);
}
 
Example #8
Source Project: mySpringBoot   Author: MyBeany   File: ExcelUtils.java    License: Apache License 2.0 6 votes vote down vote up
private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    int rowIndex = 0;
    try {
        String sheetName = data.getName();
        if (null == sheetName) {
            sheetName = "Sheet1";
        }
        XSSFSheet sheet = wb.createSheet(sheetName);
        rowIndex = writeExcel(wb, sheet, data);
        wb.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //此处需要关闭 wb 变量
        out.close();
    }
    return rowIndex;
}
 
Example #9
Source Project: MyBox   Author: Mararsh   File: EpidemicReport.java    License: Apache License 2.0 6 votes vote down vote up
public static void writeExcel(File file, List<EpidemicReport> reports, List<String> extraFields) {
    try {
        if (file == null || reports == null || reports.isEmpty()) {
            return;
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = writeExcelHeader(wb, sheet, extraFields);
        for (int i = 0; i < reports.size(); i++) {
            EpidemicReport report = reports.get(i);
            writeExcel(sheet, i, report, extraFields);
        }
        for (int i = 0; i < columns.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
    } catch (Exception e) {

    }
}
 
Example #10
Source Project: mzmine2   Author: mzmine   File: XSSFExcelWriterReader.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * writes a data[rows][columns] array to the given wb
 * 
 * @param realFilePath
 * @param data
 * @param rowsFirst true: [rows][cols] false [cols][rows]
 */
public void writeDataArrayToSheet(XSSFSheet sheet, Object[][] data, int ic, int ir,
    boolean rowsFirst) {
  // write to wb
  for (int r = 0; r < data.length; r++) {
    // all columns
    for (int c = 0; c < data[r].length; c++) {
      if (data[r][c] != null) {
        if (rowsFirst)
          writeToCell(sheet, c + ic, r + ir, data[r][c]);
        else
          writeToCell(sheet, r + ic, c + ir, data[r][c]);
      }
    }
  }
}
 
Example #11
Source Project: MyBox   Author: Mararsh   File: GeographyCode.java    License: Apache License 2.0 6 votes vote down vote up
public static void writeExcel(File file, List<GeographyCode> codes) {
    try {
        if (file == null || codes == null || codes.isEmpty()) {
            return;
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = writeExcelHeader(wb, sheet);
        for (int i = 0;
                i < codes.size();
                i++) {
            GeographyCode code = codes.get(i);
            writeExcel(sheet, i, code);
        }
        for (int i = 0;
                i < columns.size();
                i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
    } catch (Exception e) {

    }
}
 
Example #12
Source Project: bamboobsc   Author: billchen198318   File: PerspectivesDashboardExcelCommand.java    License: Apache License 2.0 6 votes vote down vote up
private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putCharts(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "perspectives-dashboard.xlsx");
	file = null;
	return oid;
}
 
Example #13
Source Project: jeewx   Author: zhangdaiscott   File: ExcelPublicUtil.java    License: Apache License 2.0 6 votes vote down vote up
/** 
 * 获取Excel2007图片 
 * @param sheet 当前sheet对象
 * @param workbook 工作簿对象 
 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData 
 */  
public static Map<String, PictureData> getSheetPictrues07(  
        XSSFSheet sheet, XSSFWorkbook workbook) {  
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();  
    for (POIXMLDocumentPart dr : sheet.getRelations()) {  
        if (dr instanceof XSSFDrawing) {  
            XSSFDrawing drawing = (XSSFDrawing) dr;  
            List<XSSFShape> shapes = drawing.getShapes();  
            for (XSSFShape shape : shapes) {  
                XSSFPicture pic = (XSSFPicture) shape;  
                XSSFClientAnchor anchor = pic.getPreferredSize();  
                CTMarker ctMarker = anchor.getFrom();  
                String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();  
                sheetIndexPicMap.put(picIndex, pic.getPictureData());  
            }  
        }  
    }  
    return sheetIndexPicMap;  
}
 
Example #14
Source Project: danyuan-application   Author: 514840279   File: GenerateDoc.java    License: Apache License 2.0 6 votes vote down vote up
private static void setDataValidationList(int j, int k, int l, int m, String data, XSSFSheet sheet) {
	// 设置下拉列表的内容
	String[] textlist = data.split(",");
	// 加载下拉列表内容
	// 设置数据有效性加载在哪个单元格上。
	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
	XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textlist);
	// 四个参数分别是:起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
	/*   CellRangeAddressList regions = new CellRangeAddressList(
	      6,5, 6,5);*/
	// 数据有效性对象
	XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
	// data_validation_list.setSuppressDropDownArrow(false);
	
	sheet.addValidationData(data_validation_list);
}
 
Example #15
Source Project: bamboobsc   Author: billchen198318   File: ObjectivesDashboardExcelCommand.java    License: Apache License 2.0 6 votes vote down vote up
private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putCharts(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "objectives-dashboard.xlsx");
	file = null;
	return oid;
}
 
Example #16
Source Project: o2oa   Author: o2oa   File: ActionTemplate.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
private void template(XSSFWorkbook workbook) throws Exception {
	XSSFSheet sheet = workbook.createSheet("人员");
	Row row = sheet.createRow(0);
	Cell cell = row.createCell(0);
	cell.setCellValue("姓名");
	cell = row.createCell(1);
	cell.setCellValue("手机号");
	cell = row.createCell(2);
	cell.setCellValue("电子邮件");
	cell = row.createCell(3);
	cell.setCellValue("唯一编码");
	cell = row.createCell(4);
	cell.setCellValue("员工号");
	cell = row.createCell(5);
	cell.setCellValue("性别");
	cell = row.createCell(6);
	cell.setCellValue("(地址)");
	CellStyle cellStyle = workbook.createCellStyle();
	cellStyle.setWrapText(true);
	IntStream.rangeClosed(0, 6).forEach(i -> {
		sheet.setDefaultColumnStyle(i, cellStyle);
	});
}
 
Example #17
Source Project: bamboobsc   Author: billchen198318   File: TimeSeriesAnalysisExcelCommand.java    License: Apache License 2.0 6 votes vote down vote up
private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putTables(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "forecast-analysis.xlsx");
	file = null;
	return oid;
}
 
Example #18
Source Project: JavaWeb   Author: tank2140896   File: PoiXSSFExcelUtil.java    License: Apache License 2.0 6 votes vote down vote up
private static XSSFWorkbook writeSheetData(List<List<String>> data,String sheetName){
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
	XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
	XSSFRow[] rows = new XSSFRow[data.size()];
	for(int i=0;i<data.size();i++){
		List<String> columns = data.get(i);
		rows[i] = xssfSheet.createRow(i);
		//xssfSheet.setDefaultColumnWidth(columnWidth);//设置列的长度
		XSSFCell[] cells = new XSSFCell[columns.size()];
		for(int j=0;j<columns.size();j++){
			cells[j] = rows[i].createCell(j);
			//cells[j].setCellStyle(XSSFCellStyle);
			cells[j].setCellValue(columns.get(j));
		}
	}
	return xssfWorkbook;
}
 
Example #19
Source Project: poi   Author: T5750   File: TestExportExcel2007.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * XSSFRichTextString.utfDecode()<br/>
 * value.contains("_x")<br/>
 * Pattern.compile("_x([0-9A-Fa-f]{4})_");
 */
private static void export2007WithStyle(String filePath) {
	try {
		OutputStream os = new FileOutputStream(filePath);
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		XSSFCell cell = sheet.createRow(0).createCell(0);
		cell.setCellValue(TestUtil.RICH_TEXT_STRINGS[0]
				+ escape(TestUtil.REGEX + TestUtil.RICH_TEXT_STRINGS[1]
						+ TestUtil.REGEX) + TestUtil.RICH_TEXT_STRINGS[2]);
		CellStyle style = sheet.getWorkbook().createCellStyle();
		XSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		style.setFont(font);
		cell.setCellStyle(style);
		// richString.applyFont(font);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 
Example #20
Source Project: Knowage-Server   Author: KnowageLabs   File: Util.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
public static XSSFWorkbook merge(XSSFWorkbook source, HSSFSheet sheet) {
	XSSFWorkbook destinationWorkbook = source;
	XSSFSheet destinationSheet = destinationWorkbook.getSheetAt(0);

	List<CellStyle> styleMap = new ArrayList<CellStyle>();

	for (short i = 0; i < destinationWorkbook.getNumCellStyles(); i++) {
		styleMap.add(destinationWorkbook.getCellStyleAt(i));
	}

	copySheetSettings(destinationSheet, sheet);
	copySheet(styleMap, sheet, destinationSheet);
	copyPictures(destinationSheet, sheet);

	refreshFormula(destinationWorkbook);

	return destinationWorkbook;
}
 
Example #21
Source Project: Knowage-Server   Author: KnowageLabs   File: Util.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * @param destination
 *            the sheet to create from the copy.
 * @param the
 *            sheet to copy.
 * @param copyStyle
 *            true copy the style.
 */
private static void copySheet(List<CellStyle> list, HSSFSheet source, XSSFSheet destination, boolean copyStyle) {
	int maxColumnNum = 0;
	List<CellStyle> styleMap = null;
	if (copyStyle) {
		styleMap = list;
	}

	for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
		HSSFRow srcRow = source.getRow(i);
		XSSFRow destRow = destination.createRow(i);

		if (srcRow != null) {

			copyRow(source, destination, srcRow, destRow, styleMap);
			if (srcRow.getLastCellNum() > maxColumnNum) {
				maxColumnNum = srcRow.getLastCellNum();
			}
		}
	}
	destination.shiftRows(destination.getFirstRowNum(), destination.getLastRowNum(), 3);
	for (int i = 0; i <= maxColumnNum; i++) {
		destination.autoSizeColumn(i);
	}
}
 
Example #22
Source Project: ureport   Author: youseries   File: XSSFExcelParser.java    License: Apache License 2.0 6 votes vote down vote up
private Span getSpan(XSSFSheet sheet,int row ,int column){
	int sheetMergeCount = sheet.getNumMergedRegions(); 
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		if(row == firstRow && column==firstColumn){  
			int lastRow = range.getLastRow();
			int rowSpan=lastRow-firstRow;
			if(rowSpan>0){
				rowSpan++;
			}
			int colSpan=lastColumn-firstColumn;
			if(colSpan>0){
				colSpan++;
			}
			return new Span(rowSpan,colSpan);
		}
	}
	return new Span(0,0);
}
 
Example #23
Source Project: poi   Author: T5750   File: ExcelExportUtil.java    License: Apache License 2.0 6 votes vote down vote up
public static void export2007(String filePath) {
	try {
		// 输出流
		OutputStream os = new FileOutputStream(filePath);
		// 工作区
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		for (int i = 0; i < 1000; i++) {
			// 创建第一个sheet
			// 生成第一行
			XSSFRow row = sheet.createRow(i);
			// 给这一行的第一列赋值
			row.createCell(0).setCellValue("column" + i);
			// 给这一行的第一列赋值
			row.createCell(1).setCellValue("column" + i);
			// System.out.println(i);
		}
		// 写文件
		wb.write(os);
		// 关闭输出流
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 
Example #24
Source Project: Open-Lowcode   Author: openlowcode   File: FlatFileExtractor.java    License: Eclipse Public License 2.0 5 votes vote down vote up
/**
 * create restrictions on the data cells
 * 
 * @param mainsheet sheet with data
 * @param restrictionsheet sheet with restriction values
 * @param column index of column (starting with zero)
 * @param nbofchoices number of choices (starting with zero)
 * @param nbofrows number of rows (starting with zero)
 */
public static  void setRestrictionsOnCell(Sheet mainsheet,Sheet restrictionsheet,int column,int nbofchoices,int nbofrows) {
	DataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet)mainsheet);
	String columnletter =  CellReference.convertNumToColString(column);
	String formula = "'"+restrictionsheet.getSheetName()+ "'!$"+columnletter+"$"+1+":$"+columnletter+"$"+nbofchoices;
	DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
	CellRangeAddressList addressList = new CellRangeAddressList(1,nbofrows,column,column);
	
	DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
	dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	dataValidation.setSuppressDropDownArrow(true);
	mainsheet.addValidationData(dataValidation);
}
 
Example #25
Source Project: frpMgr   Author: Zo3i   File: ExcelWriter.java    License: MIT License 5 votes vote down vote up
/**
 * 写入电子表格的主要流程
 * 
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process(String fileName) throws Exception {
	
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet("sheet1");
	
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream("template.xlsx");
	wb.write(os);
	os.close();

	// 生成xml文件
	File tmp = File.createTempFile("sheet", ".xml");
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();

	// 使用产生的数据替换模板
	File templateFile = new File("template.xlsx");
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	// 删除文件之前调用一下垃圾回收器,否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile() && templateFile.exists()) {
		templateFile.delete();
	}
}
 
Example #26
Source Project: lucene-solr   Author: apache   File: TestXLSXResponseWriter.java    License: Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #27
Source Project: mzmine3   Author: mzmine   File: XSSFExcelWriterReader.java    License: GNU General Public License v2.0 5 votes vote down vote up
/**
 * Returns the cell of a given sheet at icol;irow. Former values are overwritten.
 *
 * @param sheet the sheet to write on
 * @param icol index of column
 * @param irow index of row
 * @return the given cell at icol;irow
 * @see
 */
public Cell getCell(XSSFSheet sheet, int icol, int irow) {
  // try to get row
  Row row = sheet.getRow(irow);
  // if not exist: create row
  if (row == null)
    row = sheet.createRow(irow);
  // get cell
  return row.createCell(icol);
}
 
Example #28
Source Project: bamboobsc   Author: billchen198318   File: DashboardNewExcelCommand.java    License: Apache License 2.0 5 votes vote down vote up
@SuppressWarnings("unchecked")
private int putChartsKpis(XSSFWorkbook wb, XSSFSheet sh, Context context, int row) throws Exception {
	
	int chart_need_row_size = 12;
	
	// KPIs gauge chart
	row = row + 1;
	int c = 0;
	Map<String, Object> gaugeDatas = (Map<String, Object>) context.get("kpiGaugeDatas");
	List< Map<String, Object> > gaugeMapList = (List<Map<String, Object>>) gaugeDatas.get("gaugeMapList");
	for (int i=0; i<gaugeMapList.size(); i++) {
		
		// 每row 只放2個 gauge chart
		if (c>1) {
			c = 0;
		}
		if (i>0 && i % 2 == 0) {
			row += chart_need_row_size;
		}
		
		Map<String, Object> gaugeMap = gaugeMapList.get(i);
		String imageDataStr = SimpleUtils.getPNGBase64Content( (String) gaugeMap.get("data") );
		BufferedImage image = SimpleUtils.decodeToImage( imageDataStr );
		ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
		ImageIO.write( image, "png", imgBos );
		imgBos.flush();
		
		SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, c++);
		
	}
	
	row = row + chart_need_row_size;
	
	return row;
}
 
Example #29
private void processWorkbook(XSSFWorkbook workBook) {

    Iterator<XSSFSheet> sheetIter = workBook.iterator();

    while (sheetIter.hasNext()) {
      XSSFSheet sheet = sheetIter.next();
      String name = sheet.getSheetName();
      if (name.endsWith("-menu")) {
        importMenuAccess(sheet);
      } else {
        importObjectAccess(sheet);
      }
    }
  }
 
Example #30
Source Project: wandora   Author: wandora-team   File: ExcelAdjacencyListExtractor.java    License: GNU General Public License v3.0 5 votes vote down vote up
public void processSheet(XSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    rolesPerColumn = new HashMap();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        if(isFirst && FIRST_ROW_CONTAINS_ROLES) {
            processRowAsRoles(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}