コード例 #1
0
ファイル: WordExport.cs プロジェクト: AlbertAY/PlayAndStudy
        public void Begin(WorldField worldField, XWPFDocument doc)
        {
            if (worldField.FieldSourceList == null || worldField.FieldSourceList.Count <= 0)
            {
                throw new Exception("请先配置导出数据源");
            }
            var modelField = worldField.FieldSourceList.Where(it => it.SoucreType == SoucreType.Entity);

            if ((modelField?.Count() ?? 0) > 1)
            {
                throw new Exception("只能配置一个主体表信息");
            }
            //主体的字段信息替换
            if (modelField != null)
            {
                var       baseModel = modelField.FirstOrDefault();
                string    executSql = baseModel.SourceSql.Replace("{oid}", $"'{worldField.Oid}'");
                DataTable dataTable = new DataTable();
                using (SqlConnection conn = GetSqlConnection())
                {
                    dataTable.Load(conn.ExecuteReader(executSql));
                }
                if (dataTable.Rows.Count <= 0)
                {
                    throw new Exception("主题信息查询失败");
                }

                DataRow row = dataTable.Rows[0];

                List <Field> fieldList = GetFieldList(dataTable.Columns, "");

                IList <XWPFParagraph> paragraphs = doc.Paragraphs;
                foreach (XWPFParagraph xwpParagraph in paragraphs)
                {
                    foreach (XWPFRun xwprun in xwpParagraph.Runs)
                    {
                        string text = xwprun.Text.Clone().ToString();
                        if (text.Contains("加成率"))
                        {
                        }
                        foreach (Field field in fieldList)
                        {
                            if (xwprun.Text.Contains(field.SignName))
                            {
                                text = text.Replace(field.SignName, row[field.Name].ToString());
                            }
                        }
                        xwprun.ReplaceText(xwprun.Text, text);
                    }
                }
            }
            //可重复表数量
            List <FieldSource> tableSource = worldField.FieldSourceList.Where(it => it.SoucreType == SoucreType.List)?.ToList() ?? new List <FieldSource>();


            IList <XWPFTable> tables = doc.Tables;


            foreach (FieldSource item in tableSource)
            {
                string executSql = item.SourceSql.Replace("{oid}", $"'{worldField.Oid}'");

                DataTable dataTable = new DataTable();
                using (SqlConnection conn = GetSqlConnection())
                {
                    dataTable.Load(conn.ExecuteReader(executSql));
                }
                if (dataTable.Rows.Count <= 0)
                {
                    throw new Exception("数据查询失败");
                }
                List <Field> fieldList = GetFieldList(dataTable.Columns, item.TableName);

                //具体的表处理
                foreach (XWPFTable table in tables)
                {
                    XWPFTableRow row = table.Rows.FirstOrDefault();

                    Dictionary <int, string> dicTitle = GetTableField(row, item.TableName, fieldList);
                    if (dicTitle.Count <= 0)
                    {
                        continue;
                    }

                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        XWPFTableRow xWPFTableRow = table.CreateRow();
                        foreach (var title in dicTitle)
                        {
                            string columnName = fieldList.FirstOrDefault(it => it.SignName.Equals(title.Value))?.Name;
                            if (string.IsNullOrEmpty(columnName))
                            {
                                continue;
                            }
                            XWPFTableCell rowCell = xWPFTableRow.GetCell(title.Key);
                            rowCell.SetText(dataRow[columnName].ToString());
                        }
                    }
                }
            }
        }
コード例 #2
0
ファイル: WordExport.cs プロジェクト: AlbertAY/PlayAndStudy
        public void TestExport(string path)
        {
            WorldField worldField = new WorldField(new Guid("14ca7877-d988-e811-95e9-94c691050e22"));


            string sql = @"SELECT   ContractCode [合同编码] ,
        ContractName [合同名称] ,
        JbrName [合同经办人] ,
        JfProviderName 甲方单位,
        YfProviderName 乙方单位,
        SignDate 签约日期,
        FormationMode 采购方式,
        StrategicAgreement 战略协议,
        DeptName 经办部门,
        JbrName 经办人,
        TotalJsAmount 合同结算金额,
        HtAmount [合同金额(含税)],
        HtNonTaxAmount  [合同金额(不含税)],
        HtInputTaxAmount [合同进项税额],
        ISNULL(AdjustAmount_Bz, 0.00) [调整后合同金额],
        ISNULL(TotalApplyAmount_Bz, 0.00) 累计申请金额 ,
        ISNULL(TotalPaidAmount, 0.00)  累计实付金额,
        ISNULL(TotalDeductAmount_Bz, 0.00) 累计应扣金额,
        ProjectName 项目名称,
        HtTypeName 合同类型,
        PriceRatioAvg [加成率(综合)],
        TotalInvoiceAmount 累计发票金额,
        ISNULL(AdjustTotalAmount, 0.00) AdjustTotalAmount
FROM   dbo.vcl_Contract
                            WHERE   ContractGUID={oid}";

            FieldSource contract = new FieldSource(sql, "合同信息", SoucreType.Entity);


            string productList = @"SELECT  p.Code [材料编码],
        p.Name [材料名称],
        p.BrandName [品牌名称],
        p.Model [材料型号],
        p.Attribute [指标属性],
        p.Unit 单位,
        cd.Price [单价(含税)],
        cd.Count [数量],
        cd.StrategicAgreementPrice 协议单价,
        cd.NotTaxPrice [单价(不含税)],
        cd.NoTaxAmount [金额(不含税)],
        cd.Amount [金额(含税)],
        cd.TaxAmount 税额 ,
        cd.TaxRate 税率,
        cd.Remark 备注
FROM dbo.cl_ContractProductDetails cd
        INNER JOIN dbo.cl_Product p ON cd.ProductGUID = p.ProductGUID
                            WHERE ContractGUID = {oid}";

            FieldSource contractProduct = new FieldSource(productList, "合同材料", SoucreType.List);

            worldField.FieldSourceList.Add(contract);

            worldField.FieldSourceList.Add(contractProduct);

            using (FileStream fs = File.OpenRead(path))
            {
                XWPFDocument doc;
                try
                {
                    doc = new XWPFDocument(fs);
                }
                catch (Exception)
                {
                    //doc = new HWPFDocument(fs);
                    throw;
                }
                Begin(worldField, doc);
                MemoryStream memoryStream = new MemoryStream();
                doc.Write(memoryStream);
                memoryStream.Close();
                SaveToFile(memoryStream, $"11111-{DateTime.Now.ToString("yyyyMMdd-HHmmssffff")}.doc");
            }
        }