Java Code Examples for org.apache.poi.hssf.usermodel.HSSFWorkbook

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFWorkbook. 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
Source Project: ermasterr   Source File: POIUtils.java    License: Apache License 2.0 7 votes vote down vote up
public static HSSFCellStyle copyCellStyle(final HSSFWorkbook workbook, final HSSFCellStyle style) {

        final HSSFCellStyle newCellStyle = workbook.createCellStyle();

        newCellStyle.setAlignment(style.getAlignment());
        newCellStyle.setBorderBottom(style.getBorderBottom());
        newCellStyle.setBorderLeft(style.getBorderLeft());
        newCellStyle.setBorderRight(style.getBorderRight());
        newCellStyle.setBorderTop(style.getBorderTop());
        newCellStyle.setBottomBorderColor(style.getBottomBorderColor());
        newCellStyle.setDataFormat(style.getDataFormat());
        newCellStyle.setFillBackgroundColor(style.getFillBackgroundColor());
        newCellStyle.setFillForegroundColor(style.getFillForegroundColor());
        newCellStyle.setFillPattern(style.getFillPattern());
        newCellStyle.setHidden(style.getHidden());
        newCellStyle.setIndention(style.getIndention());
        newCellStyle.setLeftBorderColor(style.getLeftBorderColor());
        newCellStyle.setLocked(style.getLocked());
        newCellStyle.setRightBorderColor(style.getRightBorderColor());
        newCellStyle.setRotation(style.getRotation());
        newCellStyle.setTopBorderColor(style.getTopBorderColor());
        newCellStyle.setVerticalAlignment(style.getVerticalAlignment());
        newCellStyle.setWrapText(style.getWrapText());

        final HSSFFont font = workbook.getFontAt(style.getFontIndex());
        newCellStyle.setFont(font);

        return newCellStyle;
    }
 
Example 2
Source Project: jeecg   Source File: ExcelTempletService.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * exce表头单元格样式处理
 * @param workbook
 * @return
 */
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle titleStyle = workbook.createCellStyle();
	titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); // 设置边框样式
	titleStyle.setBorderLeft((short) 2); // 左边框
	titleStyle.setBorderRight((short) 2); // 右边框
	titleStyle.setBorderTop((short) 2); // 左边框
	titleStyle.setBorderBottom((short) 2); // 右边框
	titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); // 顶边框
	titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); // 填充的背景颜色
	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案

	return titleStyle;
}
 
Example 3
Source Project: tools   Source File: ExcelReader.java    License: MIT License 6 votes vote down vote up
/**
 * Choose excel read resolver
 *
 * @param excel Excel annotation of Excel entity
 */
private void chooseResolver(Excel excel) {
    switch (excel.type()) {
        case XLS:
            try {
                this.context.setWorkbook(new HSSFWorkbook(this.context.getInputStream()));
            } catch (IOException e) {
                throw new ExcelInitException("Init workbook error, " + e.getMessage());
            }
            break;
        case XLSX:
            Workbook workbook = StreamingReader.builder()
                    .rowCacheSize(excel.cacheRowSize())
                    .bufferSize(excel.bufferSize())
                    .open(this.context.getInputStream());
            this.context.setWorkbook(workbook);
            break;
        default:
            throw new ExcelInitException("No corresponding resolver was found");
    }
    this.readerResolver = new ReadExecutor<>();
    this.readerResolver.init(this.context);
}
 
Example 4
Source Project: jeewx   Source File: JeecgSingleExcelView.java    License: Apache License 2.0 6 votes vote down vote up
@Override
protected void buildExcelDocument(Map<String, Object> model,
                                  HSSFWorkbook hssfWorkbook, HttpServletRequest httpServletRequest,
                                  HttpServletResponse httpServletResponse) throws Exception {
    String codedFileName = "临时文件.xls";
    if (model.containsKey(POIConstants.FILE_NAME)) {
        codedFileName = (String) model.get(POIConstants.FILE_NAME)+".xls";
    }
    httpServletResponse.setHeader(
            "content-disposition",
            "attachment;filename=" + new String(codedFileName.getBytes(), "iso8859-1"));
    if (model.containsKey(POIConstants.MAP_LIST)) {
        List<Map<String, Object>> list = (List<Map<String, Object>>) model.get(POIConstants.MAP_LIST);
        for (Map<String, Object> map : list) {
            ExcelExportUtil.createSheetInUserModel2File(hssfWorkbook,
                    (ExcelTitle) map.get(POIConstants.EXCEL_TITLE),
                    (Class<?>) map.get(POIConstants.CLASS),
                    (Collection<?>) map.get(POIConstants.DATA_LIST));
        }
    } else {
        ExcelExportUtil.createSheetInUserModel2File(hssfWorkbook,
                (ExcelTitle) model.get(POIConstants.EXCEL_TITLE), (Class<?>) model.get(POIConstants.CLASS),
                (Collection<?>) model.get(POIConstants.DATA_LIST));
    }
}
 
Example 5
Source Project: ermasterr   Source File: POIUtils.java    License: Apache License 2.0 6 votes vote down vote up
public static HSSFFont copyFont(final HSSFWorkbook workbook, final HSSFFont font) {

        final HSSFFont newFont = workbook.createFont();

        // newFont.setBoldweight(font.getBoldweight());
        // newFont.setCharSet(font.getCharSet());
        // newFont.setColor(font.getColor());
        // newFont.setFontHeight(font.getFontHeight());
        // newFont.setFontHeightInPoints(font.getFontHeightInPoints());
        // newFont.setFontName(font.getFontName());
        // newFont.setItalic(font.getItalic());
        // newFont.setStrikeout(font.getStrikeout());
        // newFont.setTypeOffset(font.getTypeOffset());
        // newFont.setUnderline(font.getUnderline());

        return newFont;
    }
 
Example 6
Source Project: birt   Source File: BasicReportTest.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testRunReportWithJpegXls() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("SimpleWithJpeg.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Simple Test Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( false, sheet.isDisplayFormulas() );
		assertEquals( true, sheet.isDisplayGridlines() );
		assertEquals( true, sheet.isDisplayRowColHeadings() );
		assertEquals( true, sheet.isDisplayZeros() );
		performSimpleWithJpegTests(sheet);
		
		// Unfortunately it's not currently possible/easy to check the dimensions of images using POI
		// So the XL file has to be opened manually for verification
	} finally {
		inputStream.close();
	}
}
 
Example 7
Source Project: Shop-for-JavaWeb   Source File: ImportExcel.java    License: MIT License 6 votes vote down vote up
/**
 * 构造函数
 * @param path 导入文件对象
 * @param headerNum 标题行号,数据行号=标题行号+1
 * @param sheetIndex 工作表编号
 * @throws InvalidFormatException 
 * @throws IOException 
 */
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 
		throws InvalidFormatException, IOException {
	if (StringUtils.isBlank(fileName)){
		throw new RuntimeException("导入文档为空!");
	}else if(fileName.toLowerCase().endsWith("xls")){    
		this.wb = new HSSFWorkbook(is);    
       }else if(fileName.toLowerCase().endsWith("xlsx")){  
       	this.wb = new XSSFWorkbook(is);
       }else{  
       	throw new RuntimeException("文档格式不正确!");
       }  
	if (this.wb.getNumberOfSheets()<sheetIndex){
		throw new RuntimeException("文档中没有工作表!");
	}
	this.sheet = this.wb.getSheetAt(sheetIndex);
	this.headerNum = headerNum;
	log.debug("Initialize success.");
}
 
Example 8
Source Project: birt   Source File: StyleManagerHUtils.java    License: Eclipse Public License 1.0 6 votes vote down vote up
/**
 * Get an HSSFPalette index for a workbook that closely approximates the passed in colour.
 * @param workbook
 * The workbook for which the colour is being sought.
 * @param colour
 * The colour, in the form "rgb(<i>r</i>, <i>g</i>, <i>b</i>)".
 * @return
 * The index into the HSSFPallete for the workbook for a colour that approximates the passed in colour.
 */
private short getHColour( HSSFWorkbook workbook, String colour ) {
	int[] rgbInt = ColorUtil.getRGBs(colour);
	if( rgbInt == null ) {
		return 0;
	}
	
	byte[] rgbByte = new byte[] { (byte)rgbInt[0], (byte)rgbInt[1], (byte)rgbInt[2] };
	HSSFPalette palette = workbook.getCustomPalette();
	
	HSSFColor result = palette.findColor(rgbByte[0], rgbByte[1], rgbByte[2]);
	if( result == null) {
		if( paletteIndex > minPaletteIndex ) {
			--paletteIndex;
			palette.setColorAtIndex(paletteIndex, rgbByte[0], rgbByte[1], rgbByte[2]);
			return paletteIndex;
		} else {
			result = palette.findSimilarColor(rgbByte[0], rgbByte[1], rgbByte[2]);
		}
	}
	return result.getIndex();
}
 
Example 9
Source Project: nextreports-server   Source File: XlsResource.java    License: Apache License 2.0 6 votes vote down vote up
@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new HSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       HSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			HSSFCell cell = headerRow.createCell(col);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(new HSSFRichTextString(s));
			col++;
		}
	}		
}
 
Example 10
Source Project: easyexcel   Source File: StyleTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void poi() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\styleTest.xls");
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
    HSSFRow hssfRow = hssfSheet.getRow(0);
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    DataFormatter formatter = new DataFormatter();
    System.out.println(hssfRow.getCell(0).getNumericCellValue());
    System.out.println(hssfRow.getCell(1).getNumericCellValue());
    System.out.println(hssfRow.getCell(2).getNumericCellValue());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormatString());

}
 
Example 11
Source Project: jumbune   Source File: ExportUtil.java    License: GNU Lesser General Public License v3.0 6 votes vote down vote up
/**
 * Adds a sheet in the Workbook
 * @param worksheet the worksheet
 * @param sheetName name of sheet
 */
public static void addSheet(Worksheet worksheet, String sheetName) {
	HSSFWorkbook workbook = null;
	HSSFSheet sheet= null;
	String classSymbol = null;
	
	workbook = worksheet.getWorkbook();
	if (workbook.getSheet(sheetName) != null) {
		return;
	}
	sheet = workbook.createSheet(sheetName);
	
	Map<String, HSSFSheet> sheets = worksheet.getSheets();
	Map<String, String> sheetSymbol = worksheet.getSheetSymbol();
	
	classSymbol = sheetSymbol.get(sheetName);
	 if ( classSymbol != null) {
		 sheets.put(classSymbol, sheet);
	 } else {
		 sheets.put(sheetName, sheet);
	 }
}
 
Example 12
Source Project: ermaster-b   Source File: HistorySheetGenerator.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * {@inheritDoc}
 */
@Override
public void generate(IProgressMonitor monitor, HSSFWorkbook workbook,
		int sheetNo, boolean useLogicalNameAsSheetName,
		Map<String, Integer> sheetNameMap,
		Map<String, ObjectModel> sheetObjectMap, ERDiagram diagram,
		Map<String, LoopDefinition> loopDefinitionMap) {

	String sheetName = this.getSheetName();

	HSSFSheet newSheet = createNewSheet(workbook, sheetNo, sheetName,
			sheetNameMap);

	sheetObjectMap.put(workbook.getSheetName(workbook
			.getSheetIndex(newSheet)), new StringObjectModel(sheetName));

	this.setHistoryListData(workbook, newSheet, sheetObjectMap, diagram);
	monitor.worked(1);
}
 
Example 13
Source Project: easypoi   Source File: PoiPublicUtil.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 获取Excel2003图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, HSSFWorkbook workbook) {
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
    List<HSSFPictureData> pictures = workbook.getAllPictures();
    if (!pictures.isEmpty()) {
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);
                String picIndex = String.valueOf(anchor.getRow1()) + "_"
                                  + String.valueOf(anchor.getCol1());
                sheetIndexPicMap.put(picIndex, picData);
            }
        }
        return sheetIndexPicMap;
    } else {
        return null;
    }
}
 
Example 14
Source Project: jeasypoi   Source File: PoiPublicUtil.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 获取Excel2003图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, HSSFWorkbook workbook) {
	Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
	List<HSSFPictureData> pictures = workbook.getAllPictures();
	if (!pictures.isEmpty()) {
		for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
			HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
			if (shape instanceof HSSFPicture) {
				HSSFPicture pic = (HSSFPicture) shape;
				int pictureIndex = pic.getPictureIndex() - 1;
				HSSFPictureData picData = pictures.get(pictureIndex);
				String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());
				sheetIndexPicMap.put(picIndex, picData);
			}
		}
		return sheetIndexPicMap;
	} else {
		return null;
	}
}
 
Example 15
Source Project: birt   Source File: StyleManagerHUtils.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Override
public void addColourToFont(Workbook workbook, Font font, String colour) {
	if(colour == null) {
		return ;
	}
	if(IStyle.TRANSPARENT_VALUE.equals(colour)) {
		return ;
	}
	if(font instanceof HSSFFont) {
		HSSFFont hFont = (HSSFFont)font;
		short colourIndex = getHColour((HSSFWorkbook)workbook, colour);
		if( colourIndex > 0 ) {
			hFont.setColor(colourIndex);
		}
	}
}
 
Example 16
Source Project: Rel   Source File: TableXLS.java    License: Apache License 2.0 6 votes vote down vote up
private TupleIterator iteratorRawXLS() throws IOException {
	FileInputStream reader = new FileInputStream(file);
	HSSFWorkbook workbook = new HSSFWorkbook(reader);
	HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
	return new SpreadsheetTupleIterator(sheet.iterator()) {
		@Override
		public void close() {
			try {
				workbook.close();
			} catch (IOException e1) {
			}
			try {
				reader.close();
			} catch (IOException e) {
			}
		}			
	};
}
 
Example 17
Source Project: jeasypoi   Source File: JeecgMapExcelView.java    License: Apache License 2.0 6 votes vote down vote up
@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
	String codedFileName = "临时文件";
	Workbook workbook = ExcelExportUtil.exportExcel((ExportParams) model.get(MapExcelConstants.PARAMS), (List<ExcelExportEntity>) model.get(MapExcelConstants.ENTITY_LIST), (Collection<? extends Map<?, ?>>) model.get(MapExcelConstants.MAP_LIST));
	if (model.containsKey(MapExcelConstants.FILE_NAME)) {
		codedFileName = (String) model.get(MapExcelConstants.FILE_NAME);
	}
	if (workbook instanceof HSSFWorkbook) {
		codedFileName += HSSF;
	} else {
		codedFileName += XSSF;
	}
	if (isIE(request)) {
		codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
	} else {
		codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
	}
	response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
	ServletOutputStream out = response.getOutputStream();
	workbook.write(out);
	out.flush();
}
 
Example 18
Source Project: easypoi   Source File: JeecgMapExcelView.java    License: Apache License 2.0 6 votes vote down vote up
@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
                                       HttpServletResponse response) throws Exception {
    String codedFileName = "临时文件";
    Workbook workbook = ExcelExportUtil.exportExcel(
        (ExportParams) model.get(MapExcelConstants.PARAMS),
        (List<ExcelExportEntity>) model.get(MapExcelConstants.ENTITY_LIST),
        (Collection<? extends Map<?, ?>>) model.get(MapExcelConstants.MAP_LIST));
    if (model.containsKey(MapExcelConstants.FILE_NAME)) {
        codedFileName = (String) model.get(MapExcelConstants.FILE_NAME);
    }
    if (workbook instanceof HSSFWorkbook) {
        codedFileName += HSSF;
    } else {
        codedFileName += XSSF;
    }
    if (isIE(request)) {
        codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
    } else {
        codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
    }
    response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
    ServletOutputStream out = response.getOutputStream();
    workbook.write(out);
    out.flush();
}
 
Example 19
@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 20
Source Project: ermaster-b   Source File: POIUtils.java    License: Apache License 2.0 6 votes vote down vote up
public static List<HSSFCellStyle> copyCellStyle(HSSFWorkbook workbook,
		HSSFRow row) {
	List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

		HSSFCell cell = row.getCell(colNum);
		if (cell != null) {
			HSSFCellStyle style = cell.getCellStyle();
			HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
			cellStyleList.add(newCellStyle);
		} else {
			cellStyleList.add(null);
		}
	}

	return cellStyleList;
}
 
Example 21
protected void initializeStyleProducers( final Workbook workbook ) {
  if ( workbook instanceof HSSFWorkbook ) {
    final boolean dynamicColors =
        "true".equals( config
            .getConfigProperty( "org.pentaho.reporting.engine.classic.core.modules.output.table.xls.DynamicColors" ) );
    if ( dynamicColors ) {
      final HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
      colorProducer = new CachingExcelColorSupport( new DynamicExcelColorProducer( hssfWorkbook ) );
    } else {
      colorProducer = new CachingExcelColorSupport( new StaticExcelColorSupport() );
    }
    fontColorProducer = colorProducer;
  } else {
    colorProducer = new XSSFExcelColorProducer();
    fontColorProducer = new CachingExcelColorSupport( new StaticExcelColorSupport() );
  }

  cellStyleProducer = createCellStyleProducer( workbook );
}
 
Example 22
Source Project: tephra   Source File: ExcelImpl.java    License: MIT License 6 votes vote down vote up
@Override
public void write(String[] titles, String[] names, JSONArray array, OutputStream outputStream) {
    try (Workbook workbook = new HSSFWorkbook()) {
        Sheet sheet = workbook.createSheet();
        Row row = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++)
            row.createCell(i).setCellValue(titles[i]);
        for (int i = 0, size = array.size(); i < size; i++) {
            JSONObject object = array.getJSONObject(i);
            row = sheet.createRow(i + 1);
            for (int j = 0; j < names.length; j++)
                row.createCell(j).setCellValue(converter.toString(object.get(names[j])));
        }
        workbook.write(outputStream);
        outputStream.close();
    } catch (IOException e) {
        logger.warn(e, "输出Excel文档时发生异常!");
    }
}
 
Example 23
Source Project: cia   Source File: EsvExcelReaderImpl.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) {
	final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>();
	try {
		final HSSFWorkbook myWorkBook = createGovermentBodyWorkBook();

		for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
			addMinistryPerYearToMap(name, map, myWorkBook.getSheetAt(sheetNr));
		}

		myWorkBook.close();
	} catch (final IOException e) {
		LOGGER.warn("Problem loading", e);
	}

	return map;
}
 
Example 24
Source Project: myexcel   Source File: FileExportUtil.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 导出
 *
 * @param workbook workbook
 * @param file     file
 * @throws IOException IOException
 */
public static void export(Workbook workbook, File file) throws IOException {
    String suffix = Constants.XLSX;
    if (workbook instanceof HSSFWorkbook) {
        if (file.getName().endsWith(suffix)) {
            String absolutePath = file.getAbsolutePath();
            file = Paths.get(absolutePath.substring(0, absolutePath.length() - 1)).toFile();
        }
        suffix = Constants.XLS;
    }
    if (!file.getName().endsWith(suffix)) {
        file = Paths.get(file.getAbsolutePath() + suffix).toFile();
    }
    try (OutputStream os = new FileOutputStream(file)) {
        workbook.write(os);
    } finally {
        if (workbook instanceof SXSSFWorkbook) {
            ((SXSSFWorkbook) workbook).dispose();
        }
        workbook.close();
    }
}
 
Example 25
Source Project: jeewx   Source File: CgReportExcelServiceImpl.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * exce表头单元格样式处理
 * @param workbook
 * @return
 */
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle titleStyle = workbook.createCellStyle();
	titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
	titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
	titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 底边框
	titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 顶边框
	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 填充的背景颜色
	titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案

	return titleStyle;
}
 
Example 26
Source Project: lams   Source File: EventWorkbookBuilder.java    License: GNU General Public License v2.0 5 votes vote down vote up
public HSSFWorkbook getStubHSSFWorkbook() {
    // Create a base workbook
    HSSFWorkbook wb = HSSFWorkbook.create(getStubWorkbook());
    // Stub the sheets, so sheet name lookups work
    for (BoundSheetRecord bsr : boundSheetRecords) {
        wb.createSheet(bsr.getSheetname());
    }
    // Ready for Formula use!
    return wb;
}
 
Example 27
Source Project: spring-boot-study   Source File: ExcelUtil.java    License: MIT License 5 votes vote down vote up
/**
 *## 3.2 创建工作表 Sheet
 * - 工作表名称不要超过 31 个字符
 * - 名称不能含有特殊字符
 * - 可以使用 WorkbookUtil.createSafeSheetName 来创建安全的工作表名称
 * 返回空 输出 workbook.xls  注意此时 excel元素不全,还不能打开
 * */
public static void CreateNewSheet() {
    Workbook wb = new HSSFWorkbook();  // or new XSSFWorkbook();
    Sheet sheet1 = wb.createSheet("new sheet");
    Sheet sheet2 = wb.createSheet("new second sheet");
    String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
    Sheet sheet3 = wb.createSheet(safeName);
    try {
        OutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
    } catch (Exception e) {
        e.printStackTrace();
    }
}
 
Example 28
Source Project: java-master   Source File: ExcelReader.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * @param excelType   excel文件类型
 * @param inputStream 文件流
 * @param beanClz     待转换的bean类型
 * @param headerNum   头部标题行数
 * @param footerNum   尾部非数据行数
 */
public ExcelReader(ExcelType excelType, InputStream inputStream, Class<T> beanClz, int headerNum, int footerNum) {
    try {
        if (ExcelType.XLS == excelType) {
            this.workbook = new HSSFWorkbook(inputStream);
        } else {
            this.workbook = new XSSFWorkbook(inputStream);
        }
        this.sheet = workbook.getSheetAt(0);
        this.headerNum = headerNum;
        this.footerNum = footerNum;
        this.beanClz = beanClz;
        init();
        context = new ExcelContext();
        context.setWorkbook(this.workbook);
        context.setSheet(this.sheet);
    } catch (Exception e) {
        throw new RuntimeException("construct workbook failed", e);
    } finally {
        if (this.workbook != null) {
            try {
                this.workbook.close();
            } catch (IOException ignored) {
            }
        }
    }
}
 
Example 29
Source Project: yarg   Source File: HSSFRangeHelper.java    License: Apache License 2.0 5 votes vote down vote up
public static AreaReference getAreaForRange(HSSFWorkbook workbook, String rangeName) {
    int rangeNameIdx = workbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1) return null;

    HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx);
    return new AreaReference(aNamedRange.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
}
 
Example 30
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;
}