Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

可以在表格上下加其他行吗？ #211

Open
KWMaster opened this issue Oct 12, 2021 · 8 comments
Open

可以在表格上下加其他行吗？ #211

KWMaster opened this issue Oct 12, 2021 · 8 comments

Comments

@KWMaster
Copy link

KWMaster commented Oct 12, 2021 •
edited
Loading

比如：

第一行xxxx（占一个单元格的字符串）
第二行xxxx
EEC的表格数据
最后再追加一行或多行xxx
@wangguanquan
Copy link
Owner

暂时没有可以直接调用的接口，不过可以通过自定义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/"));
    }

@wangguanquan
Copy link
Owner

效果：

@KWMaster
Copy link
Author

可以使用，谢谢大大！

@wangguanquan
Copy link
Owner

wangguanquan commented Oct 13, 2021 via email

@KWMaster
Copy link
Author

KWMaster commented Oct 14, 2021 •
edited
Loading

可以将代码贴一下，造点假数据，方便排查问题

我在项目里引了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/"));
    }
}

@wangguanquan
Copy link
Owner

需要拉取源码然后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>

@KWMaster
Copy link
Author

需要拉取源码然后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>

谢谢，这个问题解决了
有没有excel实际写入总条数的获取方法？

@wangguanquan
Copy link
Owner

调用RowBlock#getTotla()方法即可。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@wangguanquan @KWMaster