下面列出了org.apache.poi.ss.usermodel.Workbook#write ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* Extracts to excel a tree of objects
*
* @param objecttree object trees
* @return the binary file
*/
public SFile extractToExcel(NodeTree<E> objecttree) {
try {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Export Data");
loadWorkbook(sheet, objecttree);
ByteArrayOutputStream documentinmemory = new ByteArrayOutputStream();
workbook.write(documentinmemory);
workbook.close();
SFile returnresult = new SFile("OpenLowcodeExport-" + sdf.format(new Date()) + ".xlsx",
documentinmemory.toByteArray());
return returnresult;
} catch (IOException e) {
String exceptionstring = "Exception in extracting objects to array " + definition.getName()
+ ", original IOException " + e.getMessage();
logger.severe(exceptionstring);
for (int i = 0; i < e.getStackTrace().length; i++) {
logger.severe(" " + e.getStackTrace()[i]);
}
throw new RuntimeException(exceptionstring);
}
}
@Test
public void test_FontOne() throws IOException
{
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("sheet 01");
Row row = sheet.createRow(1);
Font font = wb.createFont();
font.setBold(true);
font.setColor((short) 13);
font.setFontHeightInPoints((short) 24);
font.setFontName("宋体");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
Cell cell = row.createCell(1);
cell.setCellValue("这是测试字体格式的");
cell.setCellStyle(cellStyle);
FileOutputStream fileOutputStream = new FileOutputStream("D://font.xlsx");
wb.write(fileOutputStream);
wb.close();
}
/**
* Gets the output data.
*
* Does not require a HTTP request/response
*
* @param out
* @param reference
* @param parameters
* @throws IOException
*/
@EntityCustomAction(action = "get-export", viewKey = EntityView.VIEW_SHOW)
public ActionReturn getExport(final OutputStream out, final EntityReference reference,
final Map<String, Object> parameters) {
final String userId = getUserId(reference);
final String siteId = getSiteId(reference);
try {
if (this.sakaiProxy.hasUserSitePermission(userId, RosterFunctions.ROSTER_FUNCTION_EXPORT, siteId)) {
final RosterSite site = getSite(reference, siteId);
final Workbook workbook = getExportData(userId, site, parameters);
workbook.write(out);
out.close();
final ActionReturn actionReturn = new ActionReturn("base64",
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", out);
return actionReturn;
} else {
throw new EntityException(MSG_NO_EXPORT_PERMISSION, reference.getReference());
}
} catch (final IOException e) {
log.error(MSG_NO_FILE_CREATED, e);
throw new EntityException(MSG_NO_FILE_CREATED, reference.getReference());
}
}
@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();
}
/**
* 在指定的工作簿、行、列书写值。
* 作者：朱志杰
* Sep 19, 2012 3:25:01 PM
* @param sheetOrder 工作簿序号，基于0.
* @param colum 列 基于1
* @param row 行 基于1
* @param content 将要被书写的内容。
* @throws Exception 书写后保存异常。
*/
public void write(int sheetOrder,int colum, int row, String content) throws Exception {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if(fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
Row rows = sheet.createRow(row-1);
Cell cell = rows.createCell(colum-1);
cell.setCellValue(content);
FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}
/**
* 加密导出
*
* @param workbook workbook
* @param fileName fileName
* @param response response
* @param password password
*/
public static void encryptExport(final Workbook workbook, String fileName, HttpServletResponse response, final String password) {
if (workbook instanceof HSSFWorkbook) {
throw new IllegalArgumentException("Document encryption for.xls is not supported");
}
Path path = null;
try {
String suffix = Constants.XLSX;
path = TempFileOperator.createTempFile("encrypt_temp", suffix);
workbook.write(Files.newOutputStream(path));
final POIFSFileSystem fs = new POIFSFileSystem();
final EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
final Encryptor enc = info.getEncryptor();
enc.confirmPassword(password);
try (OPCPackage opc = OPCPackage.open(path.toFile(), PackageAccess.READ_WRITE);
OutputStream os = enc.getDataStream(fs)) {
opc.save(os);
}
if (!fileName.endsWith(suffix)) {
fileName += suffix;
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
setAttachmentConfig(fileName, response);
fs.writeFilesystem(response.getOutputStream());
} catch (IOException | InvalidFormatException | GeneralSecurityException e) {
throw new RuntimeException(e);
} finally {
clear(workbook);
TempFileOperator.deleteTempFile(path);
}
}
/**
* 写入excel并填充内容,一个sheet只能写65536行以下，超出会报异常，写入时建议使用AbstractExcel2007Writer
* @param fileName
* @throws IOException
*/
@SuppressWarnings("resource")
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();
}
public static void writeAndClose(Workbook doc, OutputStream out) throws IOException {
try {
doc.write(out);
} finally {
closeQuietly(doc);
}
}
private void excelSpreadsheet(OutputStream os, List<SignupMeetingWrapper> meetingWrappers,
String downloadType) throws IOException {
EventWorksheet worksheet = new EventWorksheet(getSakaiFacade());
worksheet.setSignupMeetingService(getSignupMeetingService());
Workbook wb = worksheet.getEventWorkbook(meetingWrappers, downloadType);
wb.write(os);
}
public MessageResult exportExcel(HttpServletRequest request, HttpServletResponse response, List<E> list, String name) throws Exception{
if(list.isEmpty()){
return MessageResult.error(-1,"没有数据");
}
String physicalPath = request.getSession().getServletContext().getRealPath("/")+"excel/";
String fileName = physicalPath+name+".xlsx";
File savefile = new File(physicalPath);
if (!savefile.exists()) {
savefile.mkdirs();
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), list.get(0).getClass(), list);
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;filename="+name+".xlsx");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream out = response.getOutputStream();
File file = new File(fileName);
InputStream in = new FileInputStream(file);
int data=in.read();
while(data!=-1){
out.write(data);
data=in.read();
}
in.close();
out.close();
file.delete();
return MessageResult.success();
}
private String createExcelContent(){
Workbook workBook = new HSSFWorkbook();
workBook.createSheet();
Sheet sheet = workBook.getSheetAt(0);
Row column = sheet.createRow(0);
Row data1 = sheet.createRow(1);
sheet.createRow(2);
Row data2 = sheet.createRow(4);
Field[] fields = TestClass.class.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
column.createCell(i, CellType.STRING).setCellValue(fields[i].getName());
}
column.getCell(5).setCellValue("test");
data1.createCell(0, CellType.BOOLEAN).setCellValue(true);
data2.createCell(0, CellType.STRING).setCellValue("True");
data1.createCell(1, CellType.NUMERIC).setCellValue(2.3);
data1.createCell(2, CellType.NUMERIC).setCellValue(3.3);
data1.createCell(3, CellType.NUMERIC).setCellValue(4.3);
data1.createCell(4, CellType.NUMERIC).setCellValue(5.3);
data2.createCell(5, CellType.NUMERIC).setCellValue("a,b,c");
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workBook.write(os);
byte[] bytes = os.toByteArray();
os.close();
return Base64.getEncoder().encodeToString(bytes);
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
private void exportIntoXLS(boolean writeBackResponseInline, String mimeType, IStatement statement, String sqlQuery, Vector extractedFields,
String exportLimit) throws EMFInternalError, IOException, FileNotFoundException, SpagoBIEngineException {
IDataStore dataStore = getDataStore(statement, sqlQuery, exportLimit);
Locale locale = (Locale) getEngineInstance().getEnv().get(EngineConstants.ENV_LOCALE);
QbeXLSExporter exp = new QbeXLSExporter(dataStore, locale);
exp.setExtractedFields(extractedFields);
int resultNumber = getResultNumber(dataStore);
Integer limit = parseExportLimit(exportLimit);
boolean showLimitExportMessage = false;
if (resultNumber > limit) {
showLimitExportMessage = true;
}
Workbook wb = exp.export(exportLimit, showLimitExportMessage);
File file = File.createTempFile("workbook", ".xls");
FileOutputStream stream = new FileOutputStream(file);
wb.write(stream);
stream.flush();
stream.close();
try {
writeBackToClient(file, null, writeBackResponseInline, "workbook.xls", mimeType);
} catch (IOException ioe) {
throw new SpagoBIEngineException("Impossible to write back the responce to the client", ioe);
} finally {
if (file != null && file.exists()) {
try {
file.delete();
} catch (Exception e) {
logger.warn("Impossible to delete temporary file " + file, e);
}
}
}
}
public void saveThisExcel(ImportParams params, Class<?> pojoClass, boolean isXSSFWorkbook, Workbook book) throws Exception {
String path = PoiPublicUtil.getWebRootPath(getSaveExcelUrl(params, pojoClass));
File savefile = new File(path);
if (!savefile.exists()) {
savefile.mkdirs();
}
SimpleDateFormat format = new SimpleDateFormat("yyyMMddHHmmss");
FileOutputStream fos = new FileOutputStream(path + "/" + format.format(new Date()) + "_" + Math.round(Math.random() * 100000) + (isXSSFWorkbook == true ? ".xlsx" : ".xls"));
book.write(fos);
fos.close();
}
@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
HttpServletResponse response) throws Exception {
String codedFileName = "临时文件";
Workbook workbook = ExcelExportUtil.exportExcel(
(TemplateExportParams) model.get(TemplateExcelConstants.PARAMS),
(Class<?>) model.get(TemplateExcelConstants.CLASS),
(List<?>) model.get(TemplateExcelConstants.LIST_DATA),
(Map<String, Object>) model.get(TemplateExcelConstants.MAP_DATA));
if (model.containsKey(NormalExcelConstants.FILE_NAME)) {
codedFileName = (String) model.get(NormalExcelConstants.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();
}
ExcelTestHelper(final String parent, boolean generateXls) throws Exception {
this.xls = generateXls;
// Create a test Excel sheet with all types of supported data
Workbook wb = generateXls ? new HSSFWorkbook() : new XSSFWorkbook();
CreationHelper creationHelper = wb.getCreationHelper();
DataFormat dataFormat = creationHelper.createDataFormat();
short fmt = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss");
CellStyle style = wb.createCellStyle();
style.setDataFormat(fmt);
Sheet sheetWithHeader = wb.createSheet("Sheet 1");
// Create header row
Row headerRow = sheetWithHeader.createRow((short) 0);
headerRow.createCell(0).setCellValue("Number");
headerRow.createCell(1).setCellValue("String1");
headerRow.createCell(2).setCellValue("String2");
headerRow.createCell(3).setCellValue("MyTime");
headerRow.createCell(4).setCellValue("Formula");
headerRow.createCell(5).setCellValue("Boolean");
headerRow.createCell(6).setCellValue("Error");
generateSheetData(sheetWithHeader, style, (short)1);
Sheet sheetWithoutHeader = wb.createSheet("Sheet 2");
generateSheetData(sheetWithoutHeader, style, (short)0);
testFilePath = new File(parent, "excelTestFile").getPath();
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(testFilePath);
wb.write(fileOut);
fileOut.close();
}
/**
* 输出客户端excel
*/
public static void responseExcel(HttpServletResponse response, Workbook workbook, String fileName) {
try (OutputStream os = response.getOutputStream()) {
// 设定字符集
response.setCharacterEncoding("UTF-8");
// 设定Content类型
response.setContentType("multipart/form-data");
// 设定Http头部
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(os);
} catch (IOException e) {
LOGGER.error("输出excel异常", e);
}
}
@Test
@SneakyThrows
public void testExport() {
ExportParams params = new ExportParams(null, "测试", ExcelType.XSSF);
Workbook workbook = ExcelExportUtil.exportExcel(params, User.class, USER_EXCEL_LIST);
File savefile = new File(OUTPUT_PATH);
if (!savefile.exists()) {
savefile.mkdirs();
}
FileOutputStream fos = new FileOutputStream(OUTPUT_PATH + "User.xlsx");
workbook.write(fos);
fos.close();
}
/**
* 写入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();
}
public void saveThisExcel(ImportParams params, Class<?> pojoClass, boolean isXSSFWorkbook,
Workbook book) throws Exception {
String path = PoiPublicUtil.getWebRootPath(getSaveExcelUrl(params, pojoClass));
File savefile = new File(path);
if (!savefile.exists()) {
savefile.mkdirs();
}
SimpleDateFormat format = new SimpleDateFormat("yyyMMddHHmmss");
FileOutputStream fos = new FileOutputStream(path + "/" + format.format(new Date()) + "_"
+ Math.round(Math.random() * 100000)
+ (isXSSFWorkbook == true ? ".xlsx" : ".xls"));
book.write(fos);
fos.close();
}
public void writeOutputStream(Workbook workbook, OutputStream out) throws IOException {
workbook.write(out);
}