Java Workbook类代码示例

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
@RequestMapping(value = "/test2.xlsx", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
@ResponseBody
byte[] testDown() throws IOException, InvalidFormatException {
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int i = 0; i < 60000; i++) {
        Row newRow = sheet.createRow(i);
        for (int j = 0; j < 100; j++) {
            newRow.createCell(j).setCellValue("test" + Math.random());
        }
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    workbook.write(os);
    byte[] bytes = os.toByteArray();
    return bytes;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
/**
 * Returns the default data cell style. Obtained from:
 * http://svn.apache.org/repos/asf/poi
 * /trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java
 *
 * @param wb the wb
 * @return the cell style
 */
protected CellStyle defaultDataCellStyle(final Workbook wb) {
    CellStyle style;
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setWrapText(true);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setDataFormat(doubleDataFormat);
    return style;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
public CellStyle createIndentationCellStyle(Workbook workbook, int s) {
	CellStyle dataStyle1 = this.createBorderCellStyle(workbook, true);
	Font dataFont = workbook.createFont();
	dataFont.setColor((short) 12);
	dataFont.setFontHeightInPoints((short) 10);
	dataStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	dataStyle1.setFillForegroundColor((short) 11);
	dataStyle1.setFont(dataFont);
	dataStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
	dataStyle1.setAlignment(HorizontalAlignment.LEFT);
	dataStyle1.setIndention(Short.valueOf(String.valueOf((s))));
	return dataStyle1;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
public void setCellStyleFont(Workbook workbook, CellStyle style, int i) {
	Font font = workbook.createFont();
	if (i == 0) {
		// 正常
	} else if (i == 4) {
		// 下划线
		font.setUnderline(Font.U_SINGLE);
		style.setFont(font);
	} else if (i == 2) {
		// 倾斜
		font.setItalic(true);
		style.setFont(font);
	} else if (i == 1) {
		// 加粗
		font.setBold(true);
		style.setFont(font);
	}
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
@Test
public void testExcel07() throws IOException{
	List<UserPlus> list = getData(0, 20000);
	Map<String, String> map = new HashMap<String,String>();
	map.put("msg", "用户信息导出报表");
	map.put("status", "导出成功");
	long start = System.currentTimeMillis();
	Workbook workbook = ExcelExportUtil.exportExcel07(UserPlus.class, list, map, true);
	long end = System.currentTimeMillis();
	System.out.println("耗时：" + (end - start ) + "毫秒");
	FileOutputStream outputStream = new FileOutputStream("D:/test/user.xlsx");
	workbook.write(outputStream);
	if(SXSSFWorkbook.class.equals(workbook.getClass())){
		SXSSFWorkbook wb = (SXSSFWorkbook)workbook;
		wb.dispose();
	}
	outputStream.flush();
	outputStream.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
/** 实际创建CellStyle Map
 * @param workbook
 * @param exportInfo
 * @param isHead
 * @return
 */
private static Map<Field, CellStyle> doCreateStyleMap(Workbook workbook, ExportInfo exportInfo,boolean isHead) {
	Map<ExportCellStyleInfo, CellStyle> tempCacheMap = new HashMap<ExportCellStyleInfo, CellStyle>();
	Map<Field, CellStyle> styleMap = new HashMap<Field, CellStyle>();
	CellStyle style;
	for(Map.Entry<Field,ExportFieldInfo> entry : exportInfo.getFieldInfoMap().entrySet()){
		ExportCellStyleInfo styleInfo ;
		if(isHead){
			styleInfo = entry.getValue().getHeadStyle();
		}else{
			styleInfo = entry.getValue().getDataStyle();
		}
		if(!StringUtils.isEmpty(entry.getValue().getDataFormat())){
			//当存在格式化时，即使是来自通用的样式，但是格式不一样，所以需要new专属格式的样式
			//由于格式化属于专属，因此也不需要放到临时缓存map之中
			style = doCreateCellStyle(workbook,styleInfo,entry.getValue().getDataFormat());			
		}else{
			style = tempCacheMap.get(styleInfo);
			if(style == null){
				style = doCreateCellStyle(workbook,styleInfo,null);
				tempCacheMap.put(styleInfo, style);
			}
		}
		if(style != null ){
			styleMap.put(entry.getKey(),style);						
		}				
	}
	tempCacheMap.clear();
	return styleMap.isEmpty() ? null : styleMap;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
@Test
public void test2() throws IOException {
    User user = new User("小红", "女", new Date());
    user.setPet(new Pet("小猫", new Date()));
    user.setAge(90);
    user.setAddress("uiijji");
    List<User> list = new ArrayList<User>();
    list.add(user);
    Workbook workbook = new HSSFWorkbook();
    //        workbook
    File file = new File("C:\\Users\\h_kx1\\Desktop\\test.xls");
    OutputStream os = new FileOutputStream(file);

    WriteExcelUtils.writeWorkBook(workbook, list);
    WriteExcelUtils.writeWorkBookToExcel(workbook, os);
    CommonUtils.closeIOStream(null, os);
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
public static <T>  ImportResult<T> importExcel(ExcelFileType fileType,InputStream inputStream,Class<T> clazz) throws Exception{
	if(importInfoMap.get(clazz) == null){//初始化信息
		initTargetClass(clazz);			
	}
	ImportInfo importInfo = importInfoMap.get(clazz);
	Integer headRow = importInfo.getHeadRow();
	Workbook workbook = createWorkbook(fileType, inputStream);
	
	int sheetNum = workbook.getNumberOfSheets();
	if(sheetNum < 1 ){
		return null;
	}
	Sheet sheet = workbook.getSheetAt(0);
	int rowCount = sheet.getPhysicalNumberOfRows();
	if(rowCount < (headRow+1)){//
		return null;
	}
	List<String> headNameList = createHeadNameList(sheet, headRow);
	return readData(clazz, importInfo, workbook,headNameList);
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
/**
 * 分批写出20w条数据
 * 
 * @throws IOException 
 */
@Test
public void batchesExport() throws IOException {
	Map<String, String> map = new HashMap<String,String>();
	map.put("msg", "用户信息导出报表");
	map.put("status", "导出成功");
	
	Workbook workbook = null;
	//分两次写入20w条数据
	List<UserPlus> list;
	for(int i = 0 ; i < 2 ; i++){
		list = getData(i*100000, 100000);
		workbook = ExcelExportUtil.exportExcel03(UserPlus.class, list, map,workbook);
		
	}
	FileOutputStream outputStream = new FileOutputStream("D:/test/user1.xls");
	workbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
/**@throws IOException 
 * @Excel(headRow=2,dataRow=5,sheetName="用户统计表",sheetSize=65536)
 * 分sheet测试  
 */
@Test
public void separateSheet() throws IOException {
	//获取10w条数据
	List<UserPlus> list = getData(0, 100000);
	Map<String, String> map = new HashMap<String,String>();
	map.put("msg", "用户信息导出报表");
	map.put("status", "导出成功");
	long start = System.currentTimeMillis();
	Workbook workbook = ExcelExportUtil.exportExcel03(UserPlus.class, list, map);
	long end = System.currentTimeMillis();
	System.out.println("耗时：" + (end - start ) + "毫秒");
	FileOutputStream outputStream = new FileOutputStream("D:/test/user.xls");
	workbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
/**
 * Creates an excel file to export message data.
 * The excel workbook will have several sheets named after the map key.
 * Each sheet of the file will have the default form:
 * <ul>
 * <li>Line 1 (Header): KEY | de | fr | more iso-language-codes ...</li>
 * <li>Line 2..n (Values): message.key | Deutsch | Francaise | other translation</li>
 * </ul>
 * The header columns are defined by the provided languages in the resources lists.
 * The concrete row and column numbers can be configured to match more requirements like comments.
 * 
 * @param input a list of message information to write to excel
 * @param output if you want to create xls (Excel 97-2003) files, DO NOT use BufferedOutputStream due to library
 *            issue.
 * @param format use constants FORMAT_EXCEL_97 or FORMAT_EXCEL_2007
 * @throws I18nException if a problem occures
 */
public void createExcel(Map<String, List<MessageResourceEntry>> input,
						OutputStream output,
						String format) throws I18nException
{
	Workbook wb = createWorkbook(format);
	for (Entry<String, List<MessageResourceEntry>> entry : input.entrySet())
	{
		createSheet(entry.getValue(), entry.getKey(), wb);
	}
	try
	{
		LOG.info("Write data to output stream");
		wb.write(output);
	}
	catch (IOException e)
	{
		throw new I18nException("Problem writing to stream", e);
	}
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
@Test
public void createExcel_keyFoundInDefaultProperty() throws Exception {
    // given

    List<String> keyList = new ArrayList<String>();
    keyList.add(KEY1);
    Map<String, Properties> localizedProperties = prepareProperties(null,
            KEY2, VALUE + "2");
    Map<String, ResourceBundle> defaultProperties = prepareDefaultProperties(
            null, KEY1, VALUE + "1");
    List<Locale> locales = prepareLocaleList(null);
    prepareFacesContextStub(Locale.GERMAN, Locale.ENGLISH);
    // when
    Workbook result = ExcelHandler.createExcel(keyList, defaultProperties,
            localizedProperties, null,
            BaseBean.LABEL_USERINTERFACE_TRANSLARIONS, locales);
    // then
    verifyCreatedResult(result, "User interface",
            "Add your language code here");
    assertEquals(VALUE + "1", result.getSheetAt(0).getRow(1).getCell(1)
            .getStringCellValue());
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
@Test
public void createExcel_keyNotFoundInDefaultProperty() throws Exception {
    // given

    List<String> keyList = new ArrayList<String>();
    keyList.add(KEY1);
    Map<String, Properties> localizedProperties = prepareProperties(null,
            KEY2, VALUE + "2");
    Map<String, ResourceBundle> defaultProperties = prepareDefaultProperties(
            null, KEY3, VALUE + "3");
    List<Locale> locales = prepareLocaleList(null);
    prepareFacesContextStub(Locale.GERMAN, Locale.ENGLISH);
    // when
    Workbook result = ExcelHandler.createExcel(keyList, defaultProperties,
            localizedProperties, null,
            BaseBean.LABEL_USERINTERFACE_TRANSLARIONS, locales);
    // then
    verifyCreatedResult(result, "User interface",
            "Add your language code here");
    assertEquals("", result.getSheetAt(0).getRow(1).getCell(1)
            .getStringCellValue());
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
@Test
public void createExcel_onlyStandardLanguages() throws Exception {
    // given

    List<String> keyList = new ArrayList<String>();
    keyList.add(KEY1);
    Map<String, ResourceBundle> defaultProperties = prepareDefaultProperties(
            null, KEY1, VALUE + "1");
    List<Locale> locales = prepareLocaleList(null);
    prepareFacesContextStub(Locale.GERMAN, Locale.ENGLISH);
    // when
    Workbook result = ExcelHandler.createExcel(keyList, defaultProperties,
            null, null, BaseBean.LABEL_USERINTERFACE_TRANSLARIONS, locales);
    // then
    verifyCreatedResult(result, "User interface",
            "Add your language code here");
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入依赖的package包/类
@Test
public void createExcel_withOneMoreStandardLanguage() throws Exception {
    // given

    List<String> keyList = new ArrayList<String>();
    keyList.add(KEY1);
    Map<String, Properties> localizedProperties = prepareProperties(DE,
            KEY1, VALUE + "1");
    Map<String, ResourceBundle> defaultProperties = prepareDefaultProperties(
            null, KEY1, VALUE + "1");
    List<Locale> locales = prepareLocaleList(DE);
    prepareFacesContextStub(Locale.GERMAN, Locale.ENGLISH);
    // when
    Workbook result = ExcelHandler.createExcel(keyList, defaultProperties,
            localizedProperties, null,
            BaseBean.LABEL_USERINTERFACE_TRANSLARIONS, locales);
    // then
    verifyCreatedResult(result, "User interface", DE);
}