我可以在流模式下使用Apache-Poi将Excel电子表格格式化为表格吗 [英] Can I use Apache-Poi to format Excel spreadsheet as a Table in Streaming Mode
本文介绍了我可以在流模式下使用Apache-Poi将Excel电子表格格式化为表格吗的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
通过选择数据和Cntl-T，可以将Excel中的常规电子表格(标签)制成表格. (这为每个列表标题提供了允许过滤值的条件)
A regular spreadsheet (tab) in Excel can be made into a table by selecting the data and Cntl-T. (This gives each column tables header that allows filtering of values)
我的电子表格是使用Apache-Poi和SXSSF流接口创建的，由于电子表格可能很大，因此我必须使用流接口.是否可以以编程方式将每张纸变成一张桌子
My spreadsheets are created using Apache-Poi and the SXSSF streaming interface, I have to use the streaming interface since the spreadsheet can be quite large. Is it possible to programmatically turn each sheet into a table
我遇到了 XSSFTable 类，但不清楚如何使用它以及是否可以在流模式下使用它.
I came across XSSFTable class but not clear how to use it and whether it can be used in streaming mode.
package com.jthink.songkong.reports.spreadsheet;
import com.jthink.songkong.db.SongCache;
import com.jthink.songkong.text.SongFieldName;
import com.jthink.songkong.ui.MainWindow;
import com.jthink.songlayer.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.hibernate.Session;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Created by Paul on 09/03/2017.
*/
public class SpreadsheetReport
{
//Max Width we want to use in chars, whatever the data
public static final int MAX_COL_WIDTH = 100;
public static final int COL_WIDTH_MULTIPLIER = 256;
private static final int FLUSH_SIZE = 1000;
public static final int FONT_MARGIN_OF_ERROR = 5;
private String reportName;
private FileOutputStream fos;
private int rowCounter =0;
private SXSSFWorkbook workbook;
private CellStyle headerStyle;
private CellStyle fieldAddedStyle;
private CellStyle fieldChangedStyle;
private CellStyle fieldDeletedStyle;
private CellStyle fieldUnchangedStyle;
private List<Worksheet> worksheets;
private CreationHelper factory;
public SpreadsheetReport(String reportName) throws IOException
{
this.reportName =reportName;
//Create new File
fos = new FileOutputStream(reportName);
workbook = new SXSSFWorkbook(FLUSH_SIZE);
factory = workbook.getCreationHelper();
worksheets = new ArrayList<Worksheet>();
worksheets.add(0, new BasicWorksheet(workbook));
worksheets.add(1, new ReleaseWorksheet(workbook));
worksheets.add(2, new ClassicalWorksheet(workbook));
worksheets.add(3, new PeopleWorksheet(workbook));
worksheets.add(4, new SortWorksheet(workbook));
worksheets.add(5, new MusicBrainzWorksheet(workbook));
worksheets.add(6, new MusicBrainzWorkWorksheet(workbook));
worksheets.add(7, new AcousticBrainzWorksheet(workbook));
Font font = workbook.createFont();
font.setBold(true);
headerStyle = workbook.createCellStyle();
headerStyle.setFont(font);
headerStyle.setWrapText(true);
fieldAddedStyle = workbook.createCellStyle();
fieldAddedStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
fieldAddedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
fieldAddedStyle.setWrapText(true);
fieldChangedStyle = workbook.createCellStyle();
fieldChangedStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
fieldChangedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
fieldChangedStyle.setWrapText(true);
fieldDeletedStyle = workbook.createCellStyle();
fieldDeletedStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
fieldDeletedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
fieldDeletedStyle.setWrapText(true);
fieldUnchangedStyle = workbook.createCellStyle();
fieldUnchangedStyle.setWrapText(true);
}
public void writeHeader() throws IOException
{
for(Worksheet next:worksheets)
{
Row r = next.getSheet().createRow(rowCounter);
for(int i=0;i <next.getMapping().size(); i++)
{
addHeaderCell(r, i, next.getMapping().get(i));
}
}
rowCounter++;
}
private void addHeaderCell(Row r, int spreadsheetIndex, SongFieldNameColumnWidth field)
{
Cell cell = r.createCell(spreadsheetIndex);
cell.setCellValue(field.getSongFieldName().getName());
cell.setCellStyle(headerStyle);
}
public void writeDatatoXlsFile(Session session, Song song, List<SongChanges> songChanges) throws IOException
{
for(Worksheet next:worksheets)
{
Row r = next.getSheet().createRow(rowCounter);
org.apache.poi.ss.usermodel.Cell c ;
c=r.createCell(0);
c.setCellValue(song.getFilename());
Map<SongFieldKey, SongChanges> fieldToChanges = new HashMap<SongFieldKey, SongChanges>();
for(SongChanges nextChange:songChanges)
{
fieldToChanges.put(nextChange.getField(), nextChange);
}
for (int i = 0; i < next.getMapping().size(); i++)
{
addFieldValue(session, next , r, i, next.getMapping().get(i), fieldToChanges.get(next.getMapping().get(i).getSongFieldName().getSongFieldKey()), song);
}
}
rowCounter++;
}
/**
* Show the old value as a comment/ttoltip
* @param r
* @param c
* @param sheet
* @param value
*/
private void addCellComment(Row r, Cell c, Worksheet sheet, String value)
{
String formattedValue = value.replace('\u0000', '\n');
int rowCount = value.split("\\\\u000").length;
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(c.getColumnIndex());
//Wider for filename column
if(c.getColumnIndex()==0)
{
anchor.setCol2(c.getColumnIndex()+5);
}
else
{
anchor.setCol2(c.getColumnIndex()+2);
}
anchor.setRow1(r.getRowNum());
anchor.setRow2(r.getRowNum()+rowCount);
// Create the comment and set the text+author
Drawing drawing = sheet.getDrawing();
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(formattedValue);
comment.setString(str);
c.setCellComment(comment);
}
private void addFieldValue(Session session, Worksheet sheet, Row r, int spreadsheetIndex, SongFieldNameColumnWidth sfncw, SongChanges fieldChanges, Song song)
{
SongFieldName songFieldName = sfncw.getSongFieldName();
String value = "";
org.apache.poi.ss.usermodel.Cell c ;
c=r.createCell(spreadsheetIndex);
//Chnanges have been made to this field
if(fieldChanges!=null)
{
SongChangeType changeType = fieldChanges.getType();
if(songFieldName==SongFieldName.FILENAME)
{
value = fieldChanges.getNewValue();
c.setCellValue(value);
c.setCellStyle(fieldChangedStyle);
//addCellComment(r, c, sheet, song.getFilename());
}
else if(songFieldName==SongFieldName.COVER_ART)
{
if (changeType == SongChangeType.ADD )
{
CoverImage ci = SongCache.findCoverImageByDataKey(session, fieldChanges.getNewValue());
if(ci!=null)
{
value = ci.getWidth() + " x " + ci.getHeight();
c.setCellValue(value);
c.setCellStyle(fieldAddedStyle);
}
else
{
MainWindow.logger.severe("Unable to find coverimage for song:"+song.getRecNo()+":"+song.getFilename());
}
session.flush();
}
else if (changeType == SongChangeType.EDIT)
{
CoverImage ci = SongCache.findCoverImageByDataKey(session, fieldChanges.getNewValue());
if(ci!=null)
{
value = ci.getWidth() + " x " + ci.getHeight();
c.setCellValue(value);
c.setCellStyle(fieldChangedStyle);
}
else
{
MainWindow.logger.severe("Unable to find coverimage for song:"+song.getRecNo()+":"+song.getFilename());
}
CoverImage ciOrig = SongCache.findCoverImageByDataKey(session, fieldChanges.getOriginalValue());
if(ciOrig!=null)
{
String valueOrig = ciOrig.getWidth() + " x " + ciOrig.getHeight();
//addCellComment(r, c, sheet, valueOrig);
}
session.flush();
}
else if (changeType == SongChangeType.NOCHANGE)
{
CoverArt coverart = song.getCoverArts().get(0);
value = coverart.getCoverImage().getWidth()+" x" + coverart.getCoverImage().getHeight();
c.setCellValue(value);
c.setCellStyle(fieldUnchangedStyle);
}
else if (changeType == SongChangeType.DELETE)
{
c.setCellStyle(fieldDeletedStyle);
}
}
else
{
if (changeType == SongChangeType.ADD)
{
value = fieldChanges.getNewValue().replace('\u0000', '\n');
c.setCellValue(value);
c.setCellStyle(fieldAddedStyle);
}
else if (changeType == SongChangeType.EDIT)
{
value = fieldChanges.getNewValue().replace('\u0000', '\n');
c.setCellValue(value);
c.setCellStyle(fieldChangedStyle);
//addCellComment(r, c, sheet, fieldChanges.getOriginalValue());
}
else if (changeType == SongChangeType.DELETE)
{
c.setCellStyle(fieldDeletedStyle);
}
else if (changeType == SongChangeType.NOCHANGE)
{
value = fieldChanges.getOriginalValue().replace('\u0000', '\n');
c.setCellValue(value);
c.setCellStyle(fieldUnchangedStyle);
//addCellComment(r, c, sheet, fieldChanges.getOriginalValue());
}
}
}
//Field is unchanged
//Note shoud not be called because we now log unchanged fields as well
else
{
if(songFieldName==SongFieldName.FILENAME)
{
value = song.getFilename();
c.setCellValue(value);
}
else if(songFieldName==SongFieldName.COVER_ART)
{
if(song.getCoverArts().size()>0)
{
CoverArt coverart = song.getCoverArts().get(0);
value = coverart.getCoverImage().getWidth()+" x" + coverart.getCoverImage().getHeight();
c.setCellValue(value);
}
}
else
{
value = song.getFieldValueOrEmptyString(songFieldName.getSongFieldKey()).replace('\u0000', '\n');
c.setCellStyle(fieldUnchangedStyle);
c.setCellValue(value);
}
}
sfncw.setColumnWidthFromValue(value);
}
public void finish() throws IOException
{
for (Worksheet next : worksheets)
{
SXSSFSheet sheet = (SXSSFSheet)next.getSheet();
for (int i = 0; i < next.getMapping().size(); i++)
{
int columnWidth = next.getMapping().get(i).getColumnWidthFromValue() + FONT_MARGIN_OF_ERROR;
columnWidth = columnWidth > MAX_COL_WIDTH ? MAX_COL_WIDTH : columnWidth;
sheet.setColumnWidth(i, columnWidth * COL_WIDTH_MULTIPLIER);
}
}
workbook.write(fos);
fos.close();
workbook.dispose();
}
}
推荐答案
刚刚发现xlsx格式只是将一系列Xml文件压缩为单个zip文件.因此，如果我尝试在Excel中为工作表手动创建表，保存更改，将其重命名为zip并解压缩，发现它创建了一个xl/tables/table1.xml并与xl/worksheets/sheet1.xml
Just found out that the xlsx format is simply a series of Xml files compressed into a single zip file. So if I tried creating a table manually within Excel for a worksheet, saving changes, renamed to zip and uncompressing and found it had a created a xl/tables/table1.xml to go with xl/worksheets/sheet1.xml
它由以下内容组成
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table1" displayName="Table1" ref="A1:M13" totalsRowShown="0" headerRowDxfId="0" dataDxfId="1">
<autoFilter ref="A1:M13"/><tableColumns count="13">
<tableColumn id="1" name="Filename"/>
<tableColumn id="2" name="Artist" dataDxfId="12"/>
<tableColumn id="3" name="Disc No" dataDxfId="11"/>
<tableColumn id="4" name="Track No" dataDxfId="10"/>
<tableColumn id="5" name="Single Disc TrackNo" dataDxfId="9"/>
<tableColumn id="6" name="Title" dataDxfId="8"/>
<tableColumn id="7" name="Album Artist" dataDxfId="7"/>
<tableColumn id="8" name="Album" dataDxfId="6"/>
<tableColumn id="9" name="Artwork"/>
<tableColumn id="10" name="Year" dataDxfId="5"/>
<tableColumn id="11" name="Original Release Year" dataDxfId="4"/>
<tableColumn id="12" name="Genre" dataDxfId="3"/>
<tableColumn id="13" name="Grouping" dataDxfId="2"/>
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>
Sheet1.xml包含
Sheet1.xml contains
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<dimension ref="A1:M13"/>
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="A6" sqref="A6"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
<cols>
<col min="1" max="1" width="69" customWidth="1"/>
<col min="2" max="2" width="20" customWidth="1"/>
<col min="3" max="3" width="12" customWidth="1"/>
<col min="4" max="4" width="13" customWidth="1"/>
<col min="5" max="5" width="24" customWidth="1"/>
<col min="6" max="6" width="32" customWidth="1"/>
<col min="7" max="7" width="18" customWidth="1"/>
<col min="8" max="8" width="23" customWidth="1"/>
<col min="9" max="9" width="13" customWidth="1"/>
<col min="10" max="10" width="9" customWidth="1"/>
<col min="11" max="11" width="26" customWidth="1"/>
<col min="12" max="12" width="36" customWidth="1"/>
<col min="13" max="13" width="13" customWidth="1"/>
</cols>
<sheetData>
<row r="1" spans="1:13" x14ac:dyDescent="0.25">
<c r="A1" s="1" t="s"><v>0</v></c>
<c r="B1" s="1" t="s"><v>1</v></c>
<c r="C1" s="1" t="s"><v>2</v></c>
................................
<c r="M13" s="3" t="s"><v>22</v></c>
</row>
</sheetData>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<tableParts count="1"><tablePart r:id="rId1"/>
</tableParts>
因此，可以通过解析关联的工作表来创建表，或者就我而言，因为我是首先创建工作表，所以我具有创建表xml文件并将其添加到zip所需的信息.
So table could be created either by parsing the associated sheet or in my case since I am creating the sheet in the first place I have the info I need to create the table xml files , and add them to the zip.
也许可以独立使用pois XSSFTable类(它具有公共构造函数)，然后不确定如何将其呈现给Xml.
It may be possible to use pois XSSFTable class independently (it has a public constructor) and then render that to Xml, not sure.
这篇关于我可以在流模式下使用Apache-Poi将Excel电子表格格式化为表格吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
