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

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFWorkbook. 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: POIUtils.java    From ermasterr with 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 File: XlsResource.java    From nextreports-server with 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 #3
Source File: ExcelPrinterBase.java    From pentaho-reporting with GNU Lesser General Public License v2.1 6 votes vote down vote up
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 #4
Source File: FileExportUtil.java    From myexcel with 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 #5
Source File: ExcelTempletService.java    From jeecg with 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 #6
Source File: POIUtils.java    From ermaster-b with 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 #7
Source File: ExcelReader.java    From tools with 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 #8
Source File: JeecgSingleExcelView.java    From jeewx with 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 #9
Source File: BoundaryPointXlsParserTest.java    From powsybl-core with Mozilla Public License 2.0 6 votes vote down vote up
@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 #10
Source File: JeecgMapExcelView.java    From easypoi with 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 #11
Source File: JeecgMapExcelView.java    From jeasypoi with 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 #12
Source File: TableXLS.java    From Rel with 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 #13
Source File: StyleManagerHUtils.java    From birt with 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 #14
Source File: POIUtils.java    From ermasterr with 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 #15
Source File: PoiPublicUtil.java    From easypoi with 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 #16
Source File: PoiPublicUtil.java    From jeasypoi with 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 #17
Source File: BasicReportTest.java    From birt with 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 #18
Source File: ImportExcel.java    From Shop-for-JavaWeb with 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 #19
Source File: EsvExcelReaderImpl.java    From cia with 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 #20
Source File: HistorySheetGenerator.java    From ermaster-b with 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 #21
Source File: ExcelImpl.java    From tephra with 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 #22
Source File: StyleTest.java    From easyexcel with 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 #23
Source File: ExportUtil.java    From jumbune with 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 #24
Source File: StyleManagerHUtils.java    From birt with 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 #25
Source File: AbstractSheetGenerator.java    From ermasterr with Apache License 2.0 5 votes vote down vote up
public static HSSFSheet createNewSheet(final HSSFWorkbook workbook, final int sheetNo, final String name, final Map<String, Integer> sheetNameMap) {
    final HSSFSheet sheet = workbook.cloneSheet(sheetNo);
    final int newSheetNo = workbook.getSheetIndex(sheet);

    workbook.setSheetName(newSheetNo, decideSheetName(name, sheetNameMap));

    return sheet;
}
 
Example #26
Source File: WordEmbedsTest.java    From sun-wordtable-read with Apache License 2.0 5 votes vote down vote up
private static void listEmbeds2(XWPFDocument doc) throws Exception {
	for (final PackagePart pPart : doc.getAllEmbedds()) {
		final String contentType = pPart.getContentType();
		System.out.println(contentType + "\n");
		if (contentType.equals("application/vnd.ms-excel")) {
			final HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());

			for (int sheet = 0; sheet < embeddedWorkbook.getNumberOfSheets(); sheet++) {
				final HSSFSheet activeSheet = embeddedWorkbook.getSheetAt(sheet);
				if (activeSheet.getSheetName().equalsIgnoreCase("Sheet1")) {
					for (int rowIndex = activeSheet.getFirstRowNum(); rowIndex <= activeSheet
							.getLastRowNum(); rowIndex++) {
						final HSSFRow row = activeSheet.getRow(rowIndex);
						for (int cellIndex = row.getFirstCellNum(); cellIndex <= row
								.getLastCellNum(); cellIndex++) {
							final HSSFCell cell = row.getCell(cellIndex);
							if (cell != null) {
								if (cell.getCellType() == Cell.CELL_TYPE_STRING)
									System.out.println("Row:" + rowIndex + " Cell:" + cellIndex + " = "
											+ cell.getStringCellValue());
								if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
									System.out.println("Row:" + rowIndex + " Cell:" + cellIndex + " = "
											+ cell.getNumericCellValue());

									cell.setCellValue(cell.getNumericCellValue() * 2); // update
																						// the
																						// value
								}
							}
						}
					}
				}
			}
		}
	}
}
 
Example #27
Source File: XlsDataSetWriter.java    From Leo with Apache License 2.0 5 votes vote down vote up
protected static HSSFCellStyle createDateCellStyle(HSSFWorkbook workbook) {
    HSSFDataFormat format = workbook.createDataFormat();
    short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT);
    HSSFCellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormatCode);
    return dateCellStyle;
}
 
Example #28
Source File: testExternalRefsSheet.java    From tools with Apache License 2.0 5 votes vote down vote up
/**
 * Test method for {@link org.spdx.spdxspreadsheet.ExternalRefsSheet#create(org.apache.poi.ss.usermodel.Workbook, java.lang.String)}.
 */
@Test
public void testCreate() {
	Workbook wb = new HSSFWorkbook();
	ExternalRefsSheet.create(wb, "External Refs");
	ExternalRefsSheet externalRefsSheet = new ExternalRefsSheet(wb, "External Refs");
	assertTrue(externalRefsSheet.verify() == null);
}
 
Example #29
Source File: Excel2003Writer.java    From o2oa with GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * 写入excel并填充内容,一个sheet只能写65536行以下,超出会报异常,写入时建议使用AbstractExcel2007Writer
 * @param fileName
 * @throws IOException
 */
public static void writeExcel(String fileName) throws IOException{
		
		// 创建excel2003对象
		Workbook wb = new HSSFWorkbook();
		
		// 设置文件放置路径和文件名
	    FileOutputStream fileOut = new FileOutputStream(fileName);
	    // 创建新的表单
	    Sheet sheet = wb.createSheet("newsheet");
	    // 创建新行
	    for(int i=0;i<20000;i++){
		    Row row = sheet.createRow(i);
		    // 创建单元格
		    Cell cell = row.createCell(0);
		    // 设置单元格值
		    cell.setCellValue(1);
		    row.createCell(1).setCellValue(1+i);
		    row.createCell(2).setCellValue(true);
		    row.createCell(3).setCellValue(0.43d);
		    row.createCell(4).setCellValue('d');
		    row.createCell(5).setCellValue("");
		    row.createCell(6).setCellValue("第七列"+i);
		    row.createCell(7).setCellValue("第八列"+i);
	    }
	    wb.write(fileOut);
	    fileOut.close();
}
 
Example #30
Source File: SecurityUtil.java    From SQLiteToExcel with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}