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

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;
    }
 

@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++;
		}
	}		
}
 

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 );
}
 

/**
 * 导出
 *
 * @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();
    }
}
 

/**
 * 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;
}
 

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;
}
 

/**
 * 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);
}
 

@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));
    }
}
 

@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());
}
 

@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();
}
 

@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();
}
 

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) {
			}
		}			
	};
}
 

@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);
		}
	}
}
 

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;
    }
 

/**
 * 获取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;
    }
}
 

/**
 * 获取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;
	}
}
 

@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();
	}
}
 

/**
 * 构造函数
 * @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.");
}
 

@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;
}
 

/**
 * {@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);
}
 

@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文档时发生异常！");
    }
}
 

@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());

}
 

/**
 * 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);
	 }
}
 

/**
 * 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();
}
 

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;
}
 

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
								}
							}
						}
					}
				}
			}
		}
	}
}
 

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;
}
 

/**
 * 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);
}
 

/**
 * 写入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();
}
 

/**
 * 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();
}