ExcelTemplate实现输入excel模板，导出结果

ExcelTemplate实现输入excel模板，导出结果

最佳答案 53678位专家为你答疑解惑

ExcelTemplate实现输入excel模板，导出结果

提供输入一个excel模板，然后替换掉部分数据，之后再输出一个excel的文档Propose a method that input a excel template and output a excel with some datas with which you want to replace the placeholder

Excel需要替换的数据，一定要用占位符“${}”包括，这样才可以替换，当然如果你想使用自己定义的占位符，请自行修改代码中的replaceTemplateWithSpecifiedData方法的代码

First, input excel template which contains some placeholders with "${}" that will be replaced with what you want. If you want to use your own placeholder, you should modify the code which is in the method replaceTemplateWithSpecifiedData.

GitHub code 完整代码

public class ExcelTemplate {    private SXSSFWorkbook writeWorkbook;    private XSSFWorkbook readWorkbook;    private XSSFSheet readSheet;    public ExcelTemplate(String filePath) {        try {            readWorkbook=new XSSFWorkbook(new FileInputStream(filePath));        } catch (IOException e) {            e.printStackTrace();        }    }    public ExcelTemplate(InputStream inputStream) {        try {            ZipSecureFile.setMinInflateRatio(-1.0d);            readWorkbook=new XSSFWorkbook(inputStream);        } catch (IOException e) {            e.printStackTrace();        }    }    public String getData(int sheetIndex, int row, int column) {        readSheet=readWorkbook.getSheetAt(sheetIndex);        String data="";        Row r=readSheet.getRow(row);        Cell c=r.getCell(column, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);        if (c==null) {        } else {            if (r.getCell(column).getCellType()==HSSFCell.CELL_TYPE_STRING) {                data=r.getCell(column).getStringCellValue();            } else if (r.getCell(column).getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {                int intData=(int) r.getCell(column).getNumericCellValue();                data=Integer.toString(intData);            }        }        return data;    }    public void replaceTemplateWithSpecifiedData(int sheetIndex, Map<String, String> replaceDataMap) throws Exception {        readSheet=readWorkbook.getSheetAt(sheetIndex);        if (readSheet !=null) {            this.replaceTemplateWithSpecifiedData(readSheet.getSheetName(), replaceDataMap);        }    }    public void replaceTemplateWithSpecifiedData(String sheetName, Map<String, String> replaceDataMap) throws Exception {        readSheet=readWorkbook.getSheet(sheetName);        if (readSheet==null) {            throw new Exception("sheet with name " + sheetName + " not found");        }        readSheet.setForceFormulaRecalculation(true);        StrSubstitutor substitutor=new StrSubstitutor(replaceDataMap, "${", "}");        Iterator<Row> rowIterator=readSheet.iterator();        while (rowIterator.hasNext()) {            Row row=rowIterator.next();            // For each row, iterate through all the columns            Iterator<Cell> cellIterator=row.cellIterator();            while (cellIterator.hasNext()) {                Cell cell=cellIterator.next();                String cellValue=null;                try {                    cellValue=cell.getStringCellValue();                } catch (Exception e) {                    try {                        cellValue=String.valueOf(cell.getNumericCellValue());                    } catch (Exception e1) {                        e1.printStackTrace();                    }                    e.printStackTrace();                }                if (!StringUtils.isEmpty(cellValue)) {                    cell.setCellValue(substitutor.replace(cellValue));                }            }        }    }    /**     * 清除所有sheet中的占位符     * Clear all the placeholders     */    public void clearRestOfPlaceholder() {        Iterator<Sheet> sheetIterator=readWorkbook.sheetIterator();        while (sheetIterator.hasNext()) {            XSSFSheet xssfSheet=(XSSFSheet) sheetIterator.next();            xssfSheet.setForceFormulaRecalculation(true);            Iterator<Row> rowIterator=xssfSheet.iterator();            while (rowIterator.hasNext()) {                Row row=rowIterator.next();                // For each row, iterate through all the columns                Iterator<Cell> cellIterator=row.cellIterator();                while (cellIterator.hasNext()) {                    Cell cell=cellIterator.next();                    String cellValue=null;                    try {                        cellValue=cell.getStringCellValue();                    } catch (Exception e) {                        try {                            cellValue=String.valueOf(cell.getNumericCellValue());                        } catch (Exception e1) {                            e1.printStackTrace();                        }                        e.printStackTrace();                    }                    if (!StringUtils.isEmpty(cellValue)) {                        if (cellValue.startsWith("${") && cellValue.endsWith("}")) {                            cell.setCellValue("");                        }                    }                }            }        }    }    /**     * 清除指定sheet中剩余的占位符     * Clear placeholders in the given sheet name     *     * @param sheetName     */    public void clearRestOfPlaceholder(String sheetName) throws Exception {        int sheetIndex=readWorkbook.getSheetIndex(sheetName);        this.clearRestOfPlaceholder(sheetIndex);    }    /**     * 清除指定sheet中剩余的占位符     *     * Clear placeholders in the given sheet index     * @param sheetIndex     */    public void clearRestOfPlaceholder(int sheetIndex) throws Exception {        readSheet=readWorkbook.getSheetAt(sheetIndex);        if (readSheet==null) {            throw new Exception("sheet with index " + sheetIndex + " not found");        }        readSheet.setForceFormulaRecalculation(true);        Iterator<Row> rowIterator=readSheet.iterator();        while (rowIterator.hasNext()) {            Row row=rowIterator.next();            // For each row, iterate through all the columns            Iterator<Cell> cellIterator=row.cellIterator();            while (cellIterator.hasNext()) {                Cell cell=cellIterator.next();                String cellValue=null;                try {                    cellValue=cell.getStringCellValue();                } catch (Exception e) {                    try {                        cellValue=String.valueOf(cell.getNumericCellValue());                    } catch (Exception e1) {                        e1.printStackTrace();                    }                    e.printStackTrace();                }                if (!StringUtils.isEmpty(cellValue)) {                    if (cellValue.startsWith("${") && cellValue.endsWith("}")) {                        cell.setCellValue("");                    }                }            }        }    }    public void writeToFile(String filePath) throws IOException {        if (StringUtils.isEmpty(filePath)) {            return;        }        FileOutputStream fout=new FileOutputStream(filePath);        this.writeToFile(fout);    }    public void writeToFile(FileOutputStream fileOutputStream) throws IOException {        if (fileOutputStream==null) {            return;        }        try {            writeWorkbook=new SXSSFWorkbook(readWorkbook);            writeWorkbook.write(fileOutputStream);        } catch (Exception e) {            e.printStackTrace();        } finally {            this.close();        }    }    public void close() throws IOException {        readWorkbook.close();        writeWorkbook.close();    }}

如何通过 VBS 操作 WINCC 在线数据控件并导出至 EXCEL（附带程序）

1概述

本文主要介绍如何通过 VBS 操作 WINCC 在线数据表格控件。开发环境：PCS7 V8.2 SP1 / WINCC 7.4 SP1使用限制：1) 时间间隔最多至 1 分钟2) 时间范围太长，数据加载可能会比较慢3) 导出文件 EXCEL 没有置顶，导出后要切换到 EXCEL 程序。扩展性：可扩展选择参数、保存导出文件、生成 PDF 等功能。本例采用将参数提前组态在控件内，运行中通过复选框来实现选择。也可完全使用脚本实现参数添加。详见附加信息>通过脚本新增参数

2运行效果

图 1 根据选择查询数据

图 2 导出数据成功

图 3 在 EXCEL 中查看导出的数据文件

图 4 在 EXCEL 中查看导出的数据文件（选择部分列）

3项目组态

3.1画面部件说明

图 5 测试画面部件说明

3.1.1 添加时间控件

添加 WINCC Activex 控件 Date and time picker，如下图：

图 6 添加时间控件

3.1.2 设置导出模板

本例设置的 excel 文件模板路径："\\"+ServerName +"\Export\Export.xlsx"，可根据项目情况采用其它导出路径和模板。如果更换了模板或路径，必须修改导出脚本内的路径。模板内主要设置了字体、数据格式、显示的小数点位数和列宽，也可再增加页眉、页脚、LOGO 等，以达到更好的显示效果。

3.2画面打开

在画面打开事件内，设置如下脚本，用于初始化画面内各控件：

Sub OnOpen() Dim ioTimeFactor,tbl1,dtpStart,dtpEndSet tbl1=ScreenItems("tbl1")Set ioTimeFactor=ScreenItems("ioTimeFactor")Set dtpStart=ScreenItems("dtpStart")Set dtpEnd=ScreenItems("dtpEnd")'将实际设置的系数显示在设定值上ioTimeFactor. OutputValue=tbl1.TimeStepFactor'设置时间系数设置 IO 域类型为输入ioTimeFactor.BoxType=1 '设定表格为开始-结束时间范围tbl1.TimeColumnRangeType=1'设置时间控件显示格式dtpStart.Format=3dtpStart.CustomFormat="yyyy/MM/dd HH:mm:ss"dtpEnd.Format=3dtpEnd.CustomFormat="yyyy/MM/dd HH:mm:ss"'设置默认时间范围为当天dtpStart.Value=FormatDateTime(Year(Now) & "/" & Month(Now) & "/" & Day (Now),1)'当天 0 点dtpEnd.Value=Now'当前时间End Sub

图 7 画面打开事件脚本

3.3选择数值参数版设计

图 8 添加参数选择复选框

设置复选框，在复选框更改事件下添加脚本：

Sub Process_OnPropertyChanged(Byval Item, Byval value) Dim i,j,tbl1Set tbl1=ScreenItems("tbl1")For i=0 To item.BoxCount-1tbl1.ValueColumnIndex=i'判断筛选框是否被选中，如果是，则设置曲线可见If (value And 2^i) > 0 Thentbl1.ValueColumnVisible=1Elsetbl1.ValueColumnVisible=0End IfNext End Sub

图 9 数据参数显示隐藏设置脚本

图 10 脚本与控件属性对应关系

3.4设置时间系数

属性：TimeStepFactor

图 11 修改时间间隔脚本

Sub InputValue_OnPropertyChanged(ByVal Item, ByVal value) Dim tbl1Set tbl1=screenitems("tbl1")tbl1.TimeStepFactor=valueEnd Sub

图 12 修改时间系数

3.5 设定时间范围

图 13 添加时间控件

在查询按钮释放左键事件里：

Sub OnLButtonUp(Byval Item, Byval Flags, Byval x, Byval y) Dim tbl1,dtpStart,dtpEndSet tbl1=ScreenItems("tbl1")Set dtpStart=ScreenItems("dtpStart")Set dtpEnd=ScreenItems("dtpEnd")'设置表格的时间范围tbl1.TimeColumnIndex=0If dtpStart.Value < dtpEnd.Value Thentbl1.TimeColumnBeginTime=dtpStart.Valuetbl1.TimeColumnEndTime=dtpEnd.ValueElseMsgbox "开始时间小于结束时间，请正确设置!",vbOKonly+vbExclamationEnd IfEnd Sub

图 14 时间范围查询按钮事件

图 15 修改起始时间范围

3.6导出

在导出按钮释放左键事件里：

Sub OnLButtonUp(Byval Item, Byval Flags, Byval x, Byval y) Dim objExcelApp,objExcelSheet,sheetname,ServerName,DataFirstRow,TagValueTagValue=1DataFirstRow=1sheetname="sheet1"ServerName=HMIRuntime.Tags("@ServerName").Read'获取表格内数据Dim tbl1,col,RowCount,rowDim Value()Dim i,j,kSet tbl1=ScreenItems("tbl1")Set row=tbl1.GetRowCollection'行RowCount=row.countRedim Value(RowCount+1,tbl1.ValueColumnCount+2)'重定义数值数组，存储时间、数值。行：记录，列：参数Set col=tbl1.GetValueColumnCollection'数值列'序号Value(0,0)="序号"For j=1 To RowCountValue(j,0)=jNext'时间列名称tbl1.TimeColumnIndex=0Value(0,1)=tbl1.TimeColumnNamek=1For i=1 To tbl1.ValueColumnCount + 1'列可见时写数,否则跳出tbl1.ValueColumnIndex=i-2If tbl1.ValueColumnVisible Or (i=1) Then '时间列，或数值列显示'数值列名称If i > 1 Thentbl1.ValueColumnIndex=i-2Value(0,k)=tbl1.ValueColumnNameEnd IfFor j=1 To RowCountValue(j,k)=tbl1.GetRow(j).celltext(i)'注意：celltext()指向数据列（不考虑是否隐藏），当 celltext()指向隐藏的列时，数据为空。cellNextk=k+1End IfNext'打开 Excel 模板Set objExcelApp=CreateObject("Excel.Application")objExcelApp.Visible=TrueSet objExcelSheet=objExcelApp.Workbooks.Open("\\"+ServerName +"\Export\Export.xlsx") '模板文件存放位置objExcelSheet.Activate'写数据至 EXCELWith objExcelApp.Worksheets(sheetname).Range(.Cells(1,1),.Cells(RowCount+1,tbl1.ValueColumnCount+2))=Value.Cells(RowCount+2,1)="导出人:".Cells(RowCount+2,2)=HMIRuntime.Tags("@CurrentUserName").Read.Cells(RowCount+3,1)="导出位置:".Cells(RowCount+3,2)=HMIRuntime.Tags("@LocalMachineName").Read.Cells(RowCount+4,1)="导出时间:".Cells(RowCount+4,2)=Now.Cells(RowCount+5,1)="数据库:".Cells(RowCount+5,2)=HMIRuntime.Tags("@DatasourceNameRT").Read.Cells(RowCount+6,1)="软件版本:".Cells(RowCount+6,2)=HMIRuntime.Tags("@ServerVersion").ReadEnd With'Msgbox "导出成功，请在 EXCEL 中查看数据",vbOKonly+vbInformation'生成新的文件,关闭 ExcelDim path1,path2,fso,filenameSet fso=CreateObject("Scripting.FileSystemObject")filename=CStr(Year(Now)) & CStr(Month(Now)) & CStr(Day(Now))& CStr(Hour(Now))& CStr(Minute(Now))& CStr(Second(Now))path1="C:\Users\wq_07\Documents\Export\"&filename&".xlsx"If fso.FileExists(path1) Then '删除重复文件fso.DeleteFile(path1)End If'objExcelSheet.ExportAsFixedFormat 0,path1 '0-xlTypePDF ,1-xlTypeXPS，直接另存发现PDF 软件不能打开文件，因此改用导出 PDF'CreateReport_RH=path1objExcelApp.Worksheets(sheetname).SaveAs path1objExcelSheet.close False'关闭 EXCEL，不保存objExcelApp.Quit'打开导出文件所在的文件夹Dim objShell,strFolderstrFolder=fso.GetParentFolderName(path1)Set objShell=CreateObject("Wscript.Shell")objshell.Run strFolderEnd Sub

图 16 导出按钮脚本

图 17 导出后自动打开文件夹

4附加信息

4.1通过脚本新增参数

Sub OnLButtonUp(ByVal Item, ByVal Flags, ByVal x, ByVal y) Dim tbl1,colSet tbl1=ScreenItems("tbl1") '新增参数Set col=tbl1.GetValueColumnCollection.AddItem("R1-Script")'参数列名col.Caption="R1-Script" '归档变量col.Provider=1'归档变量名称col.TagName="TBL\R1" '时间轴col.TimeColumn="时间"End Sub

图 18 新增参数脚本

图 19 新增参数运行效果