eec
eec copied to clipboard
Published
20 hours ago •
wangguanquan
wangguanquan
可以在表格上下加其他行吗？
暂时没有可以直接调用的接口，不过可以通过自定义IWorksheetWriter来实现。
以下示例需要从git拉取最新代码打包，v0.4.13中的XMLWorksheetWriter所有属性均为private无法直接获取。这个类封装得不够好，作用域没有分隔开使得覆写较为困难。
不过你可以直接复制示例的代码，只需要将writeBefore和writeAfter方法的 “TODO 写行数据” 替换即可
@Test public void testAppendLine() throws IOException {
new Workbook("手动追加数据示例").addSheet(new ListSheet<>(DailyPointDayDataDto.randomTestData(10))).setWorkbookWriter(new XMLWorkbookWriter() {
@Override
protected IWorksheetWriter getWorksheetWriter(org.ttzero.excel.entity.Sheet sheet) {
return new XMLWorksheetWriter(sheet) {
@Override
protected void writeBefore() throws IOException {
// The header columns
columns = sheet.getAndSortHeaderColumns();
boolean nonHeader = sheet.getNonHeader() == 1;
bw.write(Const.EXCEL_XML_DECLARATION);
// Declaration
bw.newLine();
// Root node
if (sheet.getClass().isAnnotationPresent(TopNS.class)) {
TopNS topNS = sheet.getClass().getAnnotation(TopNS.class);
bw.write('<');
bw.write(topNS.value());
String[] prefixs = topNS.prefix(), urls = topNS.uri();
for (int i = 0, len = prefixs.length; i < len; ) {
bw.write(" xmlns");
if (prefixs[i] != null && !prefixs[i].isEmpty()) {
bw.write(':');
bw.write(prefixs[i]);
}
bw.write("=\"");
bw.write(urls[i]);
if (++i < len) {
bw.write('"');
}
}
} else {
bw.write("<worksheet xmlns=\"");
bw.write(Const.SCHEMA_MAIN);
}
bw.write("\">");
// Dimension
bw.append("<dimension ref=\"A1"); // FIXME Setting the column or row's start-index
int n = 11, size = sheet.size(); // fill 11 space
if (size > 0) {
bw.write(':');
n--;
char[] col = int2Col(columns[columns.length - 1].colIndex);
bw.write(col);
n -= col.length;
bw.writeInt(size + 1);
n -= stringSize(size + 1);
}
bw.write('"');
for (; n-->0;) bw.write(32); // Fill space
bw.write("/>");
// SheetViews default value
bw.write("<sheetViews><sheetView workbookViewId=\"0\"");
if (sheet.getId() == 1) { // Default select the first worksheet
bw.write(" tabSelected=\"1\"");
}
bw.write("/></sheetViews>");
// Default row height and width
n = 6;
bw.write("<sheetFormatPr defaultRowHeight=\"15.5\" defaultColWidth=\"");
BigDecimal width = BigDecimal.valueOf(!nonHeader ? sheet.getDefaultWidth() : 8.38);
String stringWidth = width.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
n -= stringWidth.length();
bw.write(stringWidth);
bw.write('"');
for (int i = n; i-->=0;) bw.write(32); // Fill space
bw.write("/>");
// Write body data
bw.write("<sheetData>");
int xf = getDefaultStringStyle();
// TODO 写行数据 == 将下面的数据改为你需要写的即可
startRow = 1; // 想从第几行开始写(zero base)
int row = 0, col = 1;
startRow(row, col); // 传入行号和列数（这里是列总数，不是列行）
writeCustomString("插入一行信息", row + startRow, col, xf);
endRow();
row++;
startRow(row, col); // 传入行号和列号
writeCustomString("插入二行信息", row + startRow, col, xf);
endRow();
startRow += 2;
if (!nonHeader) {
columns = sheet.getAndSortHeaderColumns();
writeHeaderRow();
startRow++;
}
// <-- END
}
protected void writeCustomString(String s, int row, int column, int xf) throws IOException {
// The limit characters per cell check
if (s != null && s.length() > Const.Limit.MAX_CHARACTERS_PER_CELL) {
throw new ExcelWriteException("Characters per cell out of limit. size=" + s.length()
+ ", limit=" + Const.Limit.MAX_CHARACTERS_PER_CELL);
}
bw.write("<c r=\"");
bw.write(int2Col(column));
bw.writeInt(row);
if (StringUtil.isEmpty(s)) {
bw.write("\" s=\"");
bw.writeInt(xf);
bw.write("\"/>");
}
else {
bw.write("\" t=\"inlineStr\" s=\"");
bw.writeInt(xf);
bw.write("\"><is><t>");
bw.escapeWrite(s); // escape text
bw.write("</t></is></c>");
}
}
protected void endRow() throws IOException {
bw.write("</row>");
}
protected void writeHeaderRow() throws IOException {
// Write header
bw.write("<row r=\"");
bw.writeInt(startRow);
bw.write("\" customHeight=\"1\" ht=\"20.5\" spans=\"1:");
bw.writeInt(columns[columns.length - 1].colIndex);
bw.write("\">");
int c = 0, defaultStyleIndex = sheet.defaultHeadStyleIndex();
if (sheet.isAutoSize()) {
for (Column hc : columns) {
writeStringAutoSize(isNotEmpty(hc.getName()) ? hc.getName() : hc.key, startRow, c++, hc.getHeaderStyleIndex() == -1 ? defaultStyleIndex : hc.getHeaderStyleIndex());
}
} else {
for (Column hc : columns) {
writeString(isNotEmpty(hc.getName()) ? hc.getName() : hc.key, startRow, c++, hc.getHeaderStyleIndex() == -1 ? defaultStyleIndex : hc.getHeaderStyleIndex());
}
}
// Write header comments
for (Column hc : columns) {
if (hc.headerComment != null) {
if (comments == null) comments = sheet.createComments();
comments.addComment(new String(int2Col(hc.colIndex)) + startRow
, hc.headerComment.getTitle(), hc.headerComment.getValue());
}
}
bw.write("</row>");
}
@Override
protected void writeAfter(int total) throws IOException {
int column = 1;
startRow(total, column); // 传入行号和列号
writeCustomString("末尾插入一行信息", startRow + total, column, getDefaultStringStyle());
bw.write("</row>");
super.writeAfter(total);
}
private int getDefaultStringStyle() {
Styles styles = getWorkbook().getStyles();
return styles.of(Styles.defaultStringStyle());
}
};
}
}).writeTo(Paths.get("./out/excel/"));
}
可以将代码贴一下，造点假数据，方便排查问题
我在项目里引了maven，但是不能读到XMLWorkSheetWriter的private属性(飘红)：
private Path workSheetPath;
private ExtBufferedWriter bw;
private Sheet sheet;
private Column[] columns;
private final SharedStrings sst;
private Comments comments;
pom.xml
<dependency>
<groupId>org.ttzero</groupId>
<artifactId>eec</artifactId>
<version>0.4.13</version>
</dependency>
package eec;
import org.ttzero.excel.annotation.TopNS;
import org.ttzero.excel.entity.ExcelWriteException;
import org.ttzero.excel.entity.IWorksheetWriter;
import org.ttzero.excel.entity.Sheet;
import org.ttzero.excel.entity.Workbook;
import org.ttzero.excel.entity.e7.XMLWorkbookWriter;
import org.ttzero.excel.entity.e7.XMLWorksheetWriter;
import org.ttzero.excel.entity.style.Styles;
import org.ttzero.excel.manager.Const;
import org.ttzero.excel.util.StringUtil;
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import static org.ttzero.excel.entity.Sheet.int2Col;
import static org.ttzero.excel.util.ExtBufferedWriter.stringSize;
import static org.ttzero.excel.util.StringUtil.isNotEmpty;
/**
*/
public class AroundXMLWorkbookWriter {
public static void main(String[] args) {
List<Map<String, Object>> data = IntStream.range(1, 20).mapToObj(i -> {
HashMap<String, Object> map = new HashMap<>();
map.put("id", i);
map.put("name", "第" + i + "个");
return map;
}).collect(Collectors.toList());
List<Sheet.Column> headers = new ArrayList<>();
headers.add(new Sheet.Column("序号", "id"));
headers.add(new Sheet.Column("姓名", "name"));
new Workbook("手动追加数据示例").addSheet(data, headers.toArray(new Sheet.Column[0])).setWorkbookWriter(new XMLWorkbookWriter() {
@Override
protected IWorksheetWriter getWorksheetWriter(Sheet sheet) {
return new XMLWorksheetWriter(sheet) {
@Override
protected void writeBefore() throws IOException {
// The header columns
columns = sheet.getAndSortHeaderColumns();
boolean nonHeader = sheet.getNonHeader() == 1;
bw.write(Const.EXCEL_XML_DECLARATION);
// Declaration
bw.newLine();
// Root node
if (sheet.getClass().isAnnotationPresent(TopNS.class)) {
TopNS topNS = sheet.getClass().getAnnotation(TopNS.class);
bw.write('<');
bw.write(topNS.value());
String[] prefixs = topNS.prefix(), urls = topNS.uri();
for (int i = 0, len = prefixs.length; i < len; ) {
bw.write(" xmlns");
if (prefixs[i] != null && !prefixs[i].isEmpty()) {
bw.write(':');
bw.write(prefixs[i]);
}
bw.write("=\"");
bw.write(urls[i]);
if (++i < len) {
bw.write('"');
}
}
} else {
bw.write("<worksheet xmlns=\"");
bw.write(Const.SCHEMA_MAIN);
}
bw.write("\">");
// Dimension
bw.append("<dimension ref=\"A1"); // FIXME Setting the column or row's start-index
int n = 11, size = sheet.size(); // fill 11 space
if (size > 0) {
bw.write(':');
n--;
char[] col = int2Col(columns[columns.length - 1].colIndex);
bw.write(col);
n -= col.length;
bw.writeInt(size + 1);
n -= stringSize(size + 1);
}
bw.write('"');
for (; n-- > 0; ) bw.write(32); // Fill space
bw.write("/>");
// SheetViews default value
bw.write("<sheetViews><sheetView workbookViewId=\"0\"");
if (sheet.getId() == 1) { // Default select the first worksheet
bw.write(" tabSelected=\"1\"");
}
bw.write("/></sheetViews>");
// Default row height and width
n = 6;
bw.write("<sheetFormatPr defaultRowHeight=\"15.5\" defaultColWidth=\"");
BigDecimal width = BigDecimal.valueOf(!nonHeader ? sheet.getDefaultWidth() : 8.38);
String stringWidth = width.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
n -= stringWidth.length();
bw.write(stringWidth);
bw.write('"');
for (int i = n; i-- >= 0; ) bw.write(32); // Fill space
bw.write("/>");
// Write body data
bw.write("<sheetData>");
int xf = getDefaultStringStyle();
// TODO 写行数据 == 将下面的数据改为你需要写的即可
startRow = 1; // 想从第几行开始写(zero base)
int row = 0, col = 1;
startRow(row, col); // 传入行号和列数（这里是列总数，不是列行）
writeCustomString("插入一行信息", row + startRow, col, xf);
endRow();
row++;
startRow(row, col); // 传入行号和列号
writeCustomString("插入二行信息", row + startRow, col, xf);
endRow();
startRow += 2;
if (!nonHeader) {
columns = sheet.getAndSortHeaderColumns();
writeHeaderRow();
startRow++;
}
// <-- END
}
protected void writeCustomString(String s, int row, int column, int xf) throws IOException {
// The limit characters per cell check
if (s != null && s.length() > Const.Limit.MAX_CHARACTERS_PER_CELL) {
throw new ExcelWriteException("Characters per cell out of limit. size=" + s.length()
+ ", limit=" + Const.Limit.MAX_CHARACTERS_PER_CELL);
}
bw.write("<c r=\"");
bw.write(int2Col(column));
bw.writeInt(row);
if (StringUtil.isEmpty(s)) {
bw.write("\" s=\"");
bw.writeInt(xf);
bw.write("\"/>");
} else {
bw.write("\" t=\"inlineStr\" s=\"");
bw.writeInt(xf);
bw.write("\"><is><t>");
bw.escapeWrite(s); // escape text
bw.write("</t></is></c>");
}
}
protected void endRow() throws IOException {
bw.write("</row>");
}
protected void writeHeaderRow() throws IOException {
// Write header
bw.write("<row r=\"");
bw.writeInt(startRow);
bw.write("\" customHeight=\"1\" ht=\"20.5\" spans=\"1:");
bw.writeInt(columns[columns.length - 1].colIndex);
bw.write("\">");
int c = 0, defaultStyleIndex = sheet.defaultHeadStyleIndex();
if (sheet.isAutoSize()) {
for (Sheet.Column hc : columns) {
writeStringAutoSize(isNotEmpty(hc.getName()) ? hc.getName() : hc.key, startRow, c++, hc.getHeaderStyleIndex() == -1 ? defaultStyleIndex : hc.getHeaderStyleIndex());
}
} else {
for (Sheet.Column hc : columns) {
writeString(isNotEmpty(hc.getName()) ? hc.getName() : hc.key, startRow, c++, hc.getHeaderStyleIndex() == -1 ? defaultStyleIndex : hc.getHeaderStyleIndex());
}
}
// Write header comments
for (Sheet.Column hc : columns) {
if (hc.headerComment != null) {
if (comments == null) comments = sheet.createComments();
comments.addComment(new String(int2Col(hc.colIndex)) + startRow
, hc.headerComment.getTitle(), hc.headerComment.getValue());
}
}
bw.write("</row>");
}
@Override
protected void writeAfter(int total) throws IOException {
int column = 1;
startRow(total, column); // 传入行号和列号
writeCustomString("末尾插入一行信息", startRow + total, column, getDefaultStringStyle());
bw.write("</row>");
super.writeAfter(total);
}
private int getDefaultStringStyle() {
Styles styles = getWorkbook().getStyles();
return styles.of(Styles.defaultStringStyle());
}
};
}
}).writeTo(Paths.get("./out/excel/"));
}
}
需要拉取源码然后insert到本地()或者deploy到私库。步骤如下：
git clone https://github.com/wangguanquan/eec
cd eec
mvn clean install
POM.xml修改到v0.4.14-SNAPSHOT即可
<dependency>
<groupId>org.ttzero</groupId>
<artifactId>eec</artifactId>
<version>0.4.14-SNAPSHOT</version>
</dependency>
需要拉取源码然后insert到本地()或者deploy到私库。步骤如下：
git clone https://github.com/wangguanquan/eec cd eec mvn clean installPOM.xml修改到v0.4.14-SNAPSHOT即可
<dependency> <groupId>org.ttzero</groupId> <artifactId>eec</artifactId> <version>0.4.14-SNAPSHOT</version> </dependency>
谢谢，这个问题解决了 有没有excel实际写入总条数的获取方法？