Пример #1
0
        /// <summary>
        /// 循环插入数据
        /// </summary>
        /// <param name="startRowIndex"></param>
        /// <param name="setValueAction"></param>
        public void CirculateLetterSetValue(int startRowIndex, Func <int, ExcelRowItem, bool> setValueAction)
        {
            if (_currentSheet.LastRowNum <= startRowIndex)
            {
                _currentSheet.CreateRow(startRowIndex);
            }

            int  currentRowIndex = startRowIndex;
            bool isWhile         = false;
            int  dataIndex       = 0;

            do
            {
                ExcelRowItem item = new ExcelRowItem();
                isWhile = setValueAction.Invoke(dataIndex, item);
                if (isWhile)
                {
                    _currentSheet.CopyRow(currentRowIndex, currentRowIndex + 1);
                }

                SetRowValue(currentRowIndex, item);

                currentRowIndex++;
                dataIndex++;
            } while (isWhile);
        }
Пример #2
0
        /// <summary>
        ///     复制行
        /// </summary>
        /// <param name="rowIndex">复制行行标</param>
        /// <returns>待追加的行标增量</returns>
        public void CopyRow(int rowIndex)
        {
            rowIndex = _rowIndexAccumulation.GetCurrentRowIndex(rowIndex);

            int span = _sheet.CopyRow(rowIndex);

            _rowIndexAccumulation.Add(span);
        }
Пример #3
0
        private static void PrintPlanets(DBStar star, ISheet sheet, int i, IRow row, int j, ICell cell, DBPlanet planet)
        {
            row  = sheet.GetRow(i);
            cell = row.GetCell(j);
            cell.SetCellValue(planet.Name);
            cell = row.GetCell(j + 1) ?? row.CreateCell(j + 1);
            cell.SetCellValue(planet.MiddleDistanceValue +
                              planet.MiddleDistanceUnit.ToString());
            cell = row.GetCell(j + 2) ?? row.CreateCell(j + 2);
            cell.SetCellValue(planet.Radius);
            cell = row.GetCell(j + 3) ?? row.CreateCell(j + 3);
            cell.SetCellValue(planet.Star);
            cell = row.GetCell(j + 4) ?? row.CreateCell(j + 4);
            cell.SetCellValue(planet.Temperature);
            cell = row.GetCell(j + 5) ?? row.CreateCell(j + 5);
            cell.SetCellValue(planet.Type.ToString());
            cell = row.GetCell(j + 6) ?? row.CreateCell(j + 6);
            cell.SetCellValue(planet.InventingDate);
            cell.CellStyle.DataFormat = 14;

            cell = row.GetCell(j + 7) ?? row.CreateCell(j + 7);
            cell.SetCellValue(planet.HasAtmosphere);
            if (planet != star.Planets.Last())
            {
                row = sheet.CopyRow(i, i + 1);
            }
        }
Пример #4
0
        private static void PrintStarProp(DBStar star, ISheet sheet, ref int i, IRow row, int j, ICell cell)
        {
            cell.SetCellValue("Дата открытия");
            cell = row.GetCell(j + 1) ?? row.CreateCell(j + 1);
            cell.SetCellValue(star.InventingDate);
            cell.CellStyle.DataFormat = 14;

            row = sheet.CopyRow(i, i + 1);
            i++;
            cell = row.GetCell(j) ?? row.CreateCell(j);
            cell.SetCellValue("Галактика");
            row.CreateCell(j + 1).SetCellValue(star.Galaxy ?? "");
            row  = sheet.CreateRow(i++);
            cell = row.GetCell(j) ?? row.CreateCell(j);
            cell.SetCellValue("Радиус");
            row.CreateCell(j + 1).SetCellValue(star.Radius);
            row = sheet.CreateRow(i++);

            cell = row.GetCell(j) ?? row.CreateCell(j);
            cell.SetCellValue("Температура");
            row.CreateCell(j + 1).SetCellValue(star.Temperature);
            row = sheet.CreateRow(i++);

            cell = row.GetCell(j) ?? row.CreateCell(j);
            cell.SetCellValue("Среднее расстояние");
            row.CreateCell(j + 1).SetCellValue(star.MiddleDistanceValue);
            row = sheet.CreateRow(i++);
        }
Пример #5
0
        protected IRow GetNewRow(int rowNum, TBodyList tList)
        {
            IRow row = null;;

            if (mode == ExportMode.Fill)
            {
                switch (exportSetting.Body.RowMode)
                {
                case FillRowMode.Copy:
                    //copy current row if has more row
                    if (curTIndex < GetCount(tList) - 1)
                    {
                        sheet.CopyRow(rowNum, rowNum + 1);
                    }
                    row = sheet.GetRow(rowNum);
                    sheet.GetRow(rowNum + 1).Height = row.Height;
                    break;

                case FillRowMode.Fill:
                    row = sheet.GetRow(rowNum);
                    break;

                default:
                    row = sheet.CreateRow(rowNum);
                    break;
                }
            }
            else
            {
                row = sheet.CreateRow(rowNum);
            }

            return(row);
        }
Пример #6
0
        /// <summary>
        /// 填写表格数据
        /// </summary>
        /// <param name="tabelName"></param>
        /// <param name="data"></param>
        public void WriteTable(string tabelName, List <object> datas)
        {
            //找到所有表格标签
            var labels = Labels.Where(x => x is TableLabel label && label.Table == tabelName).Cast <TableLabel>().ToList();

            if (labels.Count == 0)
            {
                return;
            }

            //获得模板行
            //var tempRow = Sheet.GetRow(labels.First().Cell.RowIndex);
            var tempRow = labels.First().Cell.Row;

            //根据表格行数量创建空表格
            for (int i = 0; i < datas.Count - 1; i++)
            {
                Sheet.CopyRow(tempRow.RowNum, tempRow.RowNum + 1);
            }

            //填充数据
            for (int dataRowIndex = 0; dataRowIndex < datas.Count; dataRowIndex++)
            {
                var properties = datas[dataRowIndex].GetType().GetProperties();

                foreach (var label in labels)
                {
                    var currentCell = Sheet.GetCell(tempRow.RowNum + dataRowIndex, label.Cell.ColumnIndex);
                    if (currentCell == null)
                    {
                        continue;
                    }

                    if (string.IsNullOrWhiteSpace(label.Formula))
                    {//没有公式,那么只是普通的数据替换
                        FillValue(currentCell, label.Name, datas[dataRowIndex], properties);
                    }
                    else
                    {//进入公式编辑方式
                        FillFormula(currentCell, label.Formula, label => Sheet.GetCell(tempRow.RowNum + dataRowIndex, label.Cell.ColumnIndex)?.Address?.FormatAsString());
                    }
                }
            }

            //计算每列的区域
            foreach (var label in labels)
            {
                label.CellRange = new CellRangeAddress(tempRow.RowNum, tempRow.RowNum + datas.Count - 1, label.Cell.ColumnIndex, label.Cell.ColumnIndex);
            }
        }
Пример #7
0
 /// <summary>
 /// 复制行(在指定行下面复制指定数量行)
 /// </summary>
 /// <param name="rowIndex"></param>
 /// <param name="count"></param>
 public void CopyRows(int rowIndex, int count)
 {
     try
     {
         for (int i = 1; i <= count; i++)
         {
             workSheet.CopyRow(rowIndex - 1, rowIndex);
         }
     }
     catch (Exception e)
     {
         //this.KillExcelProcess(false);
         throw e;
     }
 }
Пример #8
0
        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet s = hssfworkbook.GetSheetAt(0);

            ICell cell = s.GetRow(4).GetCell(1);

            cell.CopyCellTo(3); //copy B5 to D5

            IRow c = s.GetRow(3);

            c.CopyCell(0, 1);   //copy A4 to B4

            s.CopyRow(0, 1);    //copy row A to row B, original row B will be moved to row C automatically
            WriteToFile();
        }
Пример #9
0
 private void ExpandSheet(ISheet sheet, DataRow mainRow)
 {
     for (int i = 0; i <= sheet.LastRowNum; i++)
     {
         for (int j = 0; j < sheet.GetRow(0).LastCellNum; j++)
         {
             NamedRange expandRegion = GetExpandRegion(sheet, i, j);
             if (expandRegion == null)
             {
                 ReplaceRange(sheet.GetRow(i).GetCell(j), mainRow, 1);
             }
             else
             {
                 int rowCount           = expandRegion.RowCount;
                 int colCount           = expandRegion.ColCount;
                 DataRowCollection rows = res.Data.Rows;
                 if (rows != null)
                 {
                     for (int k = 0; k < rows.Count; k++)
                     {
                         Range range = new Range(sheet, i, i + rowCount - 1, j, j + colCount - 1);
                         if (j == range.FirstCol && k + 1 < rows.Count)
                         {
                             sheet.ShiftRows(range.FirstRow, range.LastRow, rowCount, true, false);
                             for (int l = range.FirstRow; l <= range.LastRow; l++)
                             {
                                 sheet.CopyRow(l + rowCount, l);
                                 if (!autoHeight)
                                 {
                                     sheet.GetRow(l).Height = sheet.GetRow(l + rowCount).Height;
                                 }
                             }
                         }
                         ExpandRange(range, rows[k], k + 1);
                         i += rowCount;
                     }
                 }
                 else
                 {
                     ExpandRange(expandRegion, null, 1);
                 }
                 j += colCount - 1;
             }
         }
     }
 }
Пример #10
0
        /// <summary>
        /// 将数据填充到工作表中。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="data">列表数据。</param>
        /// <param name="document">解析的文档对象。</param>
        /// <param name="source">源行,即定义字段标签的行。</param>
        private static void FillSheet(ISheet sheet, IList data, ExportingDocument document, IRow source)
        {
            var index           = 0;
            var mergedCellIndex = -1;

            foreach (var item in data)
            {
                var row = index == 0 ? sheet.GetRow(source.RowNum) : sheet.CopyRow(source.RowNum, source.RowNum + index);
                foreach (var ckv in document.DataSets[0].Properties)
                {
                    ICell cell = null;
                    if ((cell = row.GetCell(ckv.Key.ColumnIndex)) == null)
                    {
                        cell = row.CreateCell(ckv.Key.ColumnIndex);
                    }

                    if (index != 0)
                    {
                        var scell = source.GetCell(ckv.Key.ColumnIndex);
                        row.Height     = source.Height;
                        cell.CellStyle = scell.CellStyle;
                    }
                    else
                    {
                        if (cell.IsMergedCell)
                        {
                            mergedCellIndex = ckv.Key.ColumnIndex;
                        }
                    }

                    var properties = TypeDescriptor.GetProperties(item);
                    var property   = properties[ckv.Value.Key];
                    if (property == null)
                    {
                        continue;
                    }

                    var value = property == null ?
                                string.Empty : property.GetValue(item);

                    SetCellValue(cell, ckv.Value, value);
                }
                index++;
            }
        }
Пример #11
0
        public override void SetValue(ISheet sheet, JToken token)
        {
            JArray data;

            if (this.Each == "$root")
            {
                data = (JArray)token;
            }
            else
            {
                data = (JArray)token[Each];
            }
            var row = sheet.GetRow(RowIndex);

            var grouprows = ExcelGroupRow.GetGroupRows(data, TreeCode);

            for (int i = 0; i < data.Count; i++)
            {
                var item = data[i];
                foreach (var cellTemplate in Cells)
                {
                    var cell = row.GetCell(cellTemplate.ColIndex);
                    var val  = item[cellTemplate.Bind];

                    cellTemplate.SetCellValue(cell, val);
                }
                if (i != data.Count - 1)
                {
                    row = sheet.CopyRow(row.RowNum, row.RowNum + 1);
                }
            }


            var groups = grouprows.Values.OrderByDescending(o => o.TreeCode).ToList();
            var b      = JsonConvert.SerializeObject(groups);

            foreach (var grouprow in groups)
            {
                sheet.GroupRow(grouprow.StartIndex + RowIndex + 1, grouprow.EndIndex + RowIndex);
            }
            if (data.Count == 0)
            {
                sheet.ShiftRows(row.RowNum + 1, sheet.LastRowNum, -1);
            }
        }
Пример #12
0
        /// <summary>
        /// 复制行
        /// </summary>
        /// <param name="sheet">sheet</param>
        /// <param name="sourceIndex">源数据行</param>
        /// <param name="targetIndex">目标行</param>
        public static void CopyRowRelative(this ISheet sheet, int sourceIndex, int targetIndex)
        {
            GuardUtils.NotNull(sheet, nameof(sheet));
            sheet.CopyRow(sourceIndex, targetIndex);
            var row         = sheet.GetRow(targetIndex);
            var columnCount = row.Cells.Count;

            for (int j = 0; j < columnCount; j++)
            {
                var cell = row.GetCell(j);
                if (cell.CellType == CellType.Formula)
                {
                    var formula = cell.CellFormula;
                    formula = CopyFormula(formula, sheet, targetIndex);
                    cell.SetCellFormula(formula);
                }
            }
        }
Пример #13
0
        protected override void Process(ISheet table)
        {
            int    rowFIO     = table.FindCellByMacros(FIOConst).RowIndex;
            int    rowNumbers = table.FindCellByMacros(numbersConst).RowIndex;
            int    colNumbers = table.FindCellByMacros(numbersConst).ColumnIndex;
            int    rowPrice   = table.FindCellByMacros(SUM).RowIndex;
            double price;

            try
            {
                IRow r = table.GetRow(rowPrice);
                price = r.GetCell(table.FindCellByMacros(SUM).ColumnIndex - 1).NumericCellValue;
            }
            catch
            {
                price = 0.0;
            }

            table.FindCellByMacros(deliveryCenterConst).SetValue(DeliveryCenter);
            table.FindCellByMacros(deliveryCenterConst).SetValue(DeliveryCenter);
            table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString());
            table.FindCellByMacros(FIOConst).SetValue(FIO);
            table.FindCellByMacros(Count).SetValue(ListBsoNumber.Count.ToString());
            table.FindCellByMacros(Count).SetValue(ListBsoNumber.Count.ToString());
            int rowIndex    = table.FindCellByMacros(SUM).RowIndex;
            int columnIndex = table.FindCellByMacros(numbersConst).ColumnIndex;

            if (ListBsoNumber.Count <= 1)
            {
                table.FindCellByMacros(SUM).SetValue((price * ListBsoNumber.Count).ToString());
                rowIndex = table.FindCellByMacros(numbersConst).RowIndex;
                table.SetCellValue(rowIndex, columnIndex, ListBsoNumber.FirstOrDefault());
            }
            else
            {
                List <long> listLong = new List <long>(ListBsoNumber.Count);
                try
                {
                    //создаем список чисел (парсим номера БСО в long)
                    foreach (var elem in ListBsoNumber)
                    {
                        listLong.Add(long.Parse(elem));
                    }
                    //сортируем
                    listLong.Sort((a, b) => a.CompareTo(b));
                    //создаем сортированный список БСО
                    List <string> newListBsoNumber = new List <string>(listLong.Count);
                    foreach (long l in listLong)
                    {
                        newListBsoNumber.Add(string.Format("{0,9:D9}", l));
                    }

                    //группируем по диапазонам
                    Dictionary <string, long> dic = new Dictionary <string, long>();
                    long temp  = listLong.FirstOrDefault();
                    long count = 0;
                    dic.Add("с    " + newListBsoNumber.FirstOrDefault(), 0);
                    for (int i = 0; i < listLong.Count; i++)
                    {
                        if (listLong[i] - temp > 1)
                        {
                            dic.Add("по " + newListBsoNumber[i - 1], count); dic.Add("с    " + newListBsoNumber[i], 0); count = 0;
                        }
                        temp = listLong[i];
                        count++;
                    }
                    dic.Add("по " + newListBsoNumber.LastOrDefault(), count);

                    //если много данных - двигаем 2 последние строчки еще ниже
                    if (dic.Count > 10)
                    {
                        table.CopyRow(rowFIO, rowFIO + dic.Count - 9);
                        table.CopyRow(rowFIO + 1, rowFIO + dic.Count - 8);
                        table.CreateRow(rowFIO);
                        table.CreateRow(rowFIO + 1);
                    }

                    ICellStyle style = table.Workbook.CreateCellStyle();
                    style.BorderRight  = BorderStyle.Thin;
                    style.BorderBottom = BorderStyle.Thin;
                    style.BorderTop    = BorderStyle.Thin;
                    style.BorderLeft   = BorderStyle.Thin;

                    //выводим весь этот колохоз
                    int r = rowNumbers;
                    foreach (var element in dic)
                    {
                        IRow row = table.CreateRow(r);
                        row.CreateCell(0).CellStyle  = style;
                        row.CreateCell(1).CellStyle  = style;
                        row.CreateCell(2).CellStyle  = style;
                        row.CreateCell(3).CellStyle  = style;
                        row.CreateCell(4).CellStyle  = style;
                        row.CreateCell(5).CellStyle  = style;
                        row.CreateCell(6).CellStyle  = style;
                        row.CreateCell(7).CellStyle  = style;
                        row.CreateCell(8).CellStyle  = style;
                        row.CreateCell(9).CellStyle  = style;
                        row.CreateCell(10).CellStyle = style;

                        row.GetCell(3).SetValue(element.Key);

                        if (element.Value > 0)
                        {//2ая строка в группе
                            row.GetCell(2).SetValue("Временные свидетельства"); row.GetCell(2).CellStyle = style;
                            row.GetCell(5).SetValue("шт.");
                            row.GetCell(6).SetValue(element.Value.ToString());
                            row.GetCell(7).SetValue(element.Value.ToString());
                            row.GetCell(8).SetValue(price.ToString());
                            row.GetCell(9).SetValue((price * element.Value).ToString());
                        }
                        else
                        {
                            //1ая строка
                        }
                        r++;
                    }
                }
                catch
                {
                }
            }
        }
Пример #14
0
        /// <summary>
        /// Формирование отчета
        /// </summary>
        /// <param name="name">Имя соединения</param>
        /// <param name="drv">Драйвер соединения</param>
        /// <param name="qname">Имя запроса</param>
        /// <param name="group">Группа запроса</param>
        /// <param name="subgroup">Подгруппа запроса</param>
        /// <param name="sql">SQL запроса</param>
        /// <param name="repId">ID отчета</param>
        /// <param name="person">Текущий пользователь</param>
        /// <returns>Путь формированного файла-отчета</returns>
        public string GenerateReport(string name, asv.Models.eDriverType drv, string sql, object [] args, string qname, string group, string subgroup, int repId, MemberPrincipal person)
        {
            this.person = person;
            GenerateParams(qname, group, subgroup);

            string cv      = null,
                   tplPath = GetTemplate(repId),
                   ext     = Path.GetExtension(tplPath),
                   path    = Path.GetFileNameWithoutExtension(tplPath) + "_" + person.Id + ext,
                   newPath = repPath + repId + @"\" + path;

            Type t = typeof(HSSFWorkbook);

            if (ext == ".xlsx")
            {
                repFormat = eOfficeFormat.OfficeXml;
                t         = typeof(XSSFWorkbook);
            }

            FileStream fs = new FileStream(repPath + repId + @"\" + tplPath, FileMode.Open, FileAccess.Read);
            IWorkbook  wb = (IWorkbook)Activator.CreateInstance(t, new object[] { fs });

            fs.Close();

            bool isComplex = false;

            ICell  cell  = null;
            IRow   row   = null;
            ISheet sheet = null;

            ComplexRows         compl     = null;
            IList <ComplexRows> complrows = new List <ComplexRows>();

            Regex rx = new Regex(@"#Запрос,?\s?(\d+)*-?\s?(\d+)*", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            Match m;

            IEnumerator cells, rows, sheets = wb.GetEnumerator();

            while (sheets.MoveNext())
            {
                sheet = (ISheet)sheets.Current;

                rows = sheet.GetEnumerator();
                while (rows.MoveNext())
                {
                    row = (IRow)rows.Current;

                    cells = row.GetEnumerator();
                    while (cells.MoveNext())
                    {
                        cell = (ICell)cells.Current;
                        try
                        {
                            cv = cell.StringCellValue;
                            m  = rx.Match(cv);
                            if (m.Success)
                            {
                                isComplex = true;
                                compl     = new ComplexRows(sheet.SheetName);
                                compl.R1  = row.RowNum;

                                compl.P1 = Misc.GetConfigValue(m.Groups[1].Value, -1);
                                compl.P2 = Misc.GetConfigValue(m.Groups[2].Value, -1);

                                if (compl.P1 != -1)
                                {
                                    compl.P1--;
                                }

                                if (compl.P2 != -1)
                                {
                                    compl.P2--;
                                }

                                break;
                            }
                            else if (cv.StartsWith("#Конец_Запрос", StringComparison.CurrentCultureIgnoreCase))
                            {
                                compl.R2 = row.RowNum;
                                complrows.Add(compl);

                                isComplex = false;
                                break;
                            }
                            else if (!isComplex)
                            {
                                InsertParam(cell);
                            }
                        }
                        catch
                        {
                        }
                    }
                }
            }

            #region запрос
            DataManager dm = new DataManager();
            dm.Person = person;

            List <dynamic> complexParams = dm.GetQData(name, drv, sql, args, -1).ToList();
            #endregion

            dynamic data;
            int     i, len = complexParams.Count() - 1,
                    rn, total;

            string sn = string.Empty;
            foreach (ComplexRows cr in complrows)
            {
                if (!sn.Equals(cr.S))
                {
                    sheet = wb.GetSheet(cr.S);
                    sn    = cr.S;
                }

                total = cr.TotalRows;

                if (cr.R2 == sheet.LastRowNum)
                {
                    sheet.CreateRow(cr.R2 + 1);
                }

                // удалить ряд 'Запрос' --> смещение -1
                sheet.RemoveRow(sheet.GetRow(cr.R1));
                sheet.ShiftRows(cr.R1 + 1, sheet.LastRowNum, -1);

                // удалить ряд 'Конец_Запрос' --> смещение -1
                cr.R2--;
                sheet.RemoveRow(sheet.GetRow(cr.R2));
                sheet.ShiftRows(cr.R2 + 1, sheet.LastRowNum, -1);

                if (cr.P2 == -1)
                {
                    cr.P2 = len;
                }

                for (i = cr.P1; i < cr.P2 - 1; ++i)
                {
                    data = complexParams[i];
                    for (rn = 0; rn < total; ++rn)
                    {
                        sheet.CopyRow(cr.R1, cr.R1 + total);
                        row = sheet.GetRow(cr.R1);

                        cells = row.GetEnumerator();
                        while (cells.MoveNext())
                        {
                            cell = (ICell)cells.Current;
                            InsertParam(cell, data);
                        }

                        cr.R1++;
                    }
                }

                // последний ряд
                data = complexParams[i];
                row  = sheet.GetRow(cr.R1);

                cells = row.GetEnumerator();
                while (cells.MoveNext())
                {
                    cell = (ICell)cells.Current;
                    InsertParam(cell, data);
                }
            }

            switch (repFormat)
            {
            case eOfficeFormat.OfficeXls:
                HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
                break;

            case eOfficeFormat.OfficeXml:
                XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
                break;
            }

            fs = new FileStream(repPath + repId + @"\" + path, FileMode.Create, FileAccess.Write);
            wb.Write(fs);
            fs.Close();

            return(path);
        }
Пример #15
0
        protected override void Process(ISheet table)
        {
            int rowNumber     = 16;
            int rowNumberDown = 27;

            string dateTime = string.Format("за отчетный период с {0} г. по {1} г.", DateTimeFrom.ToShortDateString(), DateTimeTo.ToShortDateString());

            table.FindCellByMacros(dateTimeConst).SetValue(dateTime);
            table.FindCellByMacros(deliveryPointConst).SetValue(DeliveryPoint);

            var ListHistoryGroupByBSO_IDs = ListHistory.GroupBy(a => a.Id);

            foreach (var item in ListHistoryGroupByBSO_IDs)
            {
                BSOHistoryItem temp = item.OrderBy(a => a.StatusDate).Where(b => b.Status.Id == (long)ListBSOStatusID.OnDelivery || b.Status.Id == (long)ListBSOStatusID.OnResponsible).LastOrDefault();

                if (temp != null)
                {
                    if (temp.StatusDate < DateTimeFrom)
                    {
                        BSOInfo bsoInfo = new BSOInfo()
                        {
                            TemporaryPolicyNumber = ListBSOinRangeDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
                            //TemporaryPolicyNumber = ListBSOAllDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
                            Status = new BSOStatusRef()
                            {
                                Id = temp.Status.Id
                            }
                        };
                        if (!string.IsNullOrEmpty(bsoInfo.TemporaryPolicyNumber))
                        {
                            ListBSOBeforeDate.Add(bsoInfo);
                        }
                    }
                    else
                    {
                        BSOInfo bsoInfo = new BSOInfo()
                        {
                            TemporaryPolicyNumber = ListBSOinRangeDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
                            //TemporaryPolicyNumber = ListBSOAllDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
                            Status = new BSOStatusRef()
                            {
                                Id = temp.Status.Id
                            }
                        };
                        if (!string.IsNullOrEmpty(bsoInfo.TemporaryPolicyNumber))
                        {
                            ListBSOGet.Add(bsoInfo);
                        }
                    }
                }
            }

            Dictionary <string, long> dicBefore = SortingAndGroup(ListBSOBeforeDate, new List <long>()
            {
                (long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible
            });
            Dictionary <string, long> dicRange = SortingAndGroup(ListBSOGet, new List <long>()
            {
                (long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible
            });
            Dictionary <string, long> dicAll = SortingAndGroup(ListBSOAllDate, new List <long>()
            {
                (long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible
            });
            Dictionary <string, long> dicOnClient = SortingAndGroup(ListBSOinRangeDate, new List <long>()
            {
                (long)ListBSOStatusID.OnClient
            });
            Dictionary <string, long> dicFail = SortingAndGroup(ListBSOinRangeDate, new List <long>()
            {
                (long)ListBSOStatusID.FailOnStorage, (long)ListBSOStatusID.FailOnResponsible, (long)ListBSOStatusID.FailGotoStorage
            });

            //определяем в каком словаре максимальное кол-во элементов, для того чтоб насоздавать строки
            List <int> countDictionary = new List <int>();

            countDictionary.Add(dicBefore == null ? 0 : dicBefore.Count);
            countDictionary.Add(dicRange == null ? 0 : dicRange.Count);
            countDictionary.Add(dicAll == null ? 0 : dicAll.Count);
            countDictionary.Add(dicOnClient == null ? 0 : dicOnClient.Count);
            countDictionary.Add(dicFail == null ? 0 : dicFail.Count);
            var max = countDictionary.Max() / 2;

            //если много данных - двигаем последние строчки еще ниже
            if (max > 10)
            {
                for (int i = 0; i < 10; i++)
                {
                    table.CopyRow(i + rowNumberDown, i + rowNumberDown + max);
                }
            }

            ICellStyle style = table.Workbook.CreateCellStyle();

            style.BorderRight  = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderTop    = BorderStyle.Thin;
            style.BorderLeft   = BorderStyle.Thin;

            List <IRow> rows = new List <IRow>();

            for (int i = 0; i < countDictionary.Max() / 2; i++)
            {
                IRow row = table.CreateRow(i + rowNumber);
                for (int j = 0; j <= 14; j++)
                {
                    row.CreateCell(j).CellStyle = style;
                }
                rows.Add(row);
            }

            Output(dicBefore, rows, 0);
            Output(dicRange, rows, 3);
            Output(dicOnClient, rows, 6);
            Output(dicFail, rows, 9);
            Output(dicAll, rows, 12);

            //Итого:
            IRow rowSum = table.CreateRow(max + rowNumber);

            rowSum.CreateCell(0).SetValue("Итого:");
            rowSum.CreateCell(2).SetCellFormula(string.Format("SUM(C{0}:C{1})", rowNumber + 1, rowNumber + max));
            rowSum.CreateCell(5).SetCellFormula(string.Format("SUM(F{0}:F{1})", rowNumber + 1, rowNumber + max));
            rowSum.CreateCell(8).SetCellFormula(string.Format("SUM(I{0}:I{1})", rowNumber + 1, rowNumber + max));
            rowSum.CreateCell(11).SetCellFormula(string.Format("SUM(L{0}:L{1})", rowNumber + 1, rowNumber + max));
            rowSum.CreateCell(14).SetCellFormula(string.Format("SUM(O{0}:O{1})", rowNumber + 1, rowNumber + max));

            table.FindCellByMacros(FIOConst).SetValue(FIO);
            table.FindCellByMacros(PositionConst).SetValue(Position);
            table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString());
        }
        /// <summary>
        /// 供应商客服提取报减信息
        /// </summary>
        /// <param name="getParam"></param>
        /// <returns></returns>
        public Common.ClientResult.UrlResult SupplierExport([FromBody] GetDataParam getParam)
        {
            FileStream   file     = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/供应商客服报减数据提取模版.xls"), FileMode.Open, FileAccess.Read);
            HSSFWorkbook workbook = new HSSFWorkbook(file);

            try
            {
                string search = getParam.search;
                int    getid  = 0;
                int    total  = 0;

                Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());

                if (queryDic != null && queryDic.Count > 0)
                {
                    if (queryDic.ContainsKey("CollectState") && !string.IsNullOrWhiteSpace(queryDic["CollectState"]))
                    {
                        string str = queryDic["CollectState"];
                        if (str.Contains(Common.CollectState.未提取.ToString()))
                        {
                            string state = Common.EmployeeAdd_State.待供应商客服提取.ToString();
                            search += "State&" + state + "^";
                        }
                        else
                        {
                            string state = Common.EmployeeAdd_State.已发送供应商.ToString();
                            search += "State&" + state + "^";
                        }
                    }
                }
                search += "UserID_Supplier&" + LoginInfo.UserID + "^";

                List <SupplierAddView> queryData = m_BLL.GetEmployeeStopExcelListBySupplier(search).OrderBy(c => c.CertificateNumber).ThenBy(c => c.CityID).ThenBy(c => c.OperationTime).ToList();
                if (queryData.Count == 0)
                {
                    var data = new Common.ClientResult.UrlResult
                    {
                        Code    = ClientCode.FindNull,
                        Message = "没有符合条件的数据",
                        URL     = ""
                    };
                    return(data);
                }


                //string excelName = "供应商导出" + DateTime.Now.ToString();
                using (MemoryStream ms = new MemoryStream())
                {
                    string ids      = string.Empty;
                    int[]  intArray = new int[queryData.Count];

                    //员工社保一览
                    ISheet sheet = workbook.GetSheetAt(0);
                    sheet.SetActiveCell(0, 0);
                    int rowNum = 0;

                    ICellStyle style = workbook.CreateCellStyle();
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
                    style.FillPattern         = FillPattern.SolidForeground;

                    string tmpCertificateNumber = "";
                    string tmpCity      = "";
                    string tmpYearMonth = "";
                    string strRemark    = "";

                    IRow currentRow = null;

                    #region 数据输出
                    int i = 0;
                    foreach (var query in queryData)
                    {
                        intArray[i] = query.EmployeeAddId ?? 0;
                        i++;
                        if (tmpCertificateNumber != query.CertificateNumber || tmpCity != query.City ||
                            (tmpYearMonth != Convert.ToDateTime(query.OperationTime).ToString("yyyyMM") && query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金))
                        {
                            //保存临时数据
                            tmpCertificateNumber = query.CertificateNumber;
                            tmpCity      = query.City;
                            tmpYearMonth = Convert.ToDateTime(query.OperationTime).ToString("yyyyMM");

                            if (currentRow != null)
                            {
                                currentRow.GetCell(10).SetCellValue(strRemark);  // 备注
                                strRemark = "";
                            }

                            rowNum++;

                            currentRow = sheet.CopyRow(rowNum + 1, rowNum);

                            currentRow.GetCell(0).SetCellValue(rowNum);                  // 序号
                            currentRow.GetCell(1).SetCellValue(query.CustomerName);      // 客服姓名
                            currentRow.GetCell(2).SetCellValue(query.City);              // 缴费地
                            currentRow.GetCell(3).SetCellValue(query.BranchName);        // 分公司名称
                            currentRow.GetCell(4).SetCellValue(query.CompanyName);       // 客户单位名称
                            currentRow.GetCell(5).SetCellValue(query.EmployeeName);      // 员工姓名
                            currentRow.GetCell(6).SetCellValue(query.CertificateNumber); // 身份证号码
                            currentRow.GetCell(7).SetCellValue(query.Telephone);         // 联系电话
                            if (query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金)
                            {
                                currentRow.GetCell(8).SetCellValue(tmpYearMonth);  // 缴费开始时间
                            }
                            else
                            {
                                currentRow.GetCell(9).SetCellValue(tmpYearMonth);  // 缴费开始时间
                            }
                        }
                        int cellNum = 0;
                        switch (Convert.ToInt32(query.InsuranceKindId))
                        {
                        case (int)Common.EmployeeAdd_InsuranceKindId.公积金:
                            cellNum = 9;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.养老:
                            cellNum = 8;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.医疗:
                            cellNum = 8;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.失业:
                            cellNum = 8;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.工伤:
                            cellNum = 8;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.生育:
                            cellNum = 8;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.大病:
                            cellNum = 8;
                            break;

                        default:
                            break;
                        }
                        if (query.State == "1")
                        {
                            currentRow.GetCell(cellNum).CellStyle = style;
                        }

                        if (!string.IsNullOrEmpty(query.Remark))
                        {
                            strRemark += query.Remark + Convert.ToChar(10);
                        }

                        ids += query.EmployeeAddId + ",";
                    }
                    #endregion

                    var results = 0;//返回的结果
                    if (queryDic["CollectState"].Equals(Common.CollectState.未提取.ToString()))
                    {
                        int[]    ApprovedId;
                        string[] strArray = ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                        ApprovedId = Array.ConvertAll <string, int>(strArray, s => int.Parse(s));

                        string returnValue = string.Empty;
                        if (ApprovedId != null && ApprovedId.Length > 0)
                        {
                            if (m_BLL.EmployeeStopPaymentApproved(ref validationErrors, ApprovedId, Common.EmployeeStopPayment_State.待供应商客服提取.ToString(), Common.EmployeeStopPayment_State.已发送供应商.ToString(), null, LoginInfo.LoginName))
                            {
                                results = ApprovedId.Count();
                                LogClassModels.WriteServiceLog("供应商客服已提取" + ",信息的Id为" + string.Join(",", ApprovedId), "消息"
                                                               );//回退成功,写入日志
                            }
                            else
                            {
                                if (validationErrors != null && validationErrors.Count > 0)
                                {
                                    validationErrors.All(a =>
                                    {
                                        returnValue += a.ErrorMessage;
                                        return(true);
                                    });
                                }
                                LogClassModels.WriteServiceLog("供应商客服提取失败" + ",信息的Id为" + string.Join(",", ApprovedId) + "," + returnValue, "消息"
                                                               );//回退失败,写入日志
                            }
                        }
                    }
                    string fileName = "供应商客服导出_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
                    string urlPath  = "DataExport\\" + fileName;                                     // 文件下载的URL地址,供给前台下载
                    string filePath = System.Web.HttpContext.Current.Server.MapPath("\\") + urlPath; // 文件路径
                    file = new FileStream(filePath, FileMode.Create);
                    workbook.Write(file);
                    file.Close();

                    string Message = queryDic["CollectState"].Equals(Common.CollectState.未提取.ToString()) ? "已成功提取报减信息" : "导出成功";

                    if (queryData.Count == 0)
                    {
                        //ActionResult result =
                        var data = new Common.ClientResult.UrlResult
                        {
                            Code    = ClientCode.FindNull,
                            Message = "没有符合条件的数据",
                            URL     = urlPath
                        };
                        return(data);
                    }
                    return(new Common.ClientResult.UrlResult
                    {
                        Code = ClientCode.Succeed,
                        Message = Message,
                        URL = urlPath
                    });
                }
            }
            catch (Exception e)
            {
                file.Close();
                return(new Common.ClientResult.UrlResult
                {
                    Code = ClientCode.Fail,
                    Message = e.Message
                });
            }
        }
Пример #17
0
        public static MemoryStream ExportIntoFile <TViewModel>(Stream stream,
                                                               bool isXlsx,
                                                               string sheetName,
                                                               IList <TViewModel> viewModels,
                                                               IList <CustomField> customData = null,
                                                               int headerRowOffset            = 3,
                                                               bool createRow        = true,
                                                               bool copyRow          = false,
                                                               bool useOriginalTypes = false)
        {
            var output = new MemoryStream();
            var tmpl   = stream;

            stream.Seek(0, SeekOrigin.Begin);
            IWorkbook workbook = null;

            if (isXlsx)
            {
                workbook = new XSSFWorkbook(tmpl);
            }
            else
            {
                workbook = new HSSFWorkbook(tmpl);
            }

            ISheet sheet = null;

            if (!String.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName); //"Template");
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }

            if (viewModels.Any())
            {
                var type = viewModels[0].GetType();

                var properties = GetProperties <TViewModel>(type);

                properties = properties.OrderBy(p => GetExcelAttribute(p).Order).ToList();
                var propertyToAttribute =
                    properties.Select(
                        p => new Tuple <PropertyInfo, ExcelSerializableAttribute>(p, GetExcelAttribute(p))).ToList();

                for (var i = 0; i < viewModels.Count; i++)
                {
                    IRow row;
                    if (copyRow)
                    {
                        if (i > 1)
                        {
                            row = sheet.CopyRow(headerRowOffset + 1, headerRowOffset + i);
                        }
                        row = sheet.GetRow(i + headerRowOffset);
                    }
                    else
                    {
                        if (createRow)
                        {
                            row = sheet.CreateRow(i + headerRowOffset);
                        }
                        else
                        {
                            row = sheet.GetRow(i + headerRowOffset);
                        }
                    }
                    WriteViewModelToRow(viewModels[i], row, propertyToAttribute, createRow, useOriginalTypes);
                }

                if (customData != null)
                {
                    foreach (var field in customData)
                    {
                        var row  = sheet.GetRow(field.Row);
                        var cell = row.GetCell(field.Cell);
                        cell.SetCellValue(field.Value);
                    }
                }

                HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
                workbook.Write(output);
            }
            return(output);
        }
Пример #18
0
        // export via template
        public static ISheet EntityListToSheetByTemplate <TEntity>(
            [NotNull] ISheet sheet,
            IEnumerable <TEntity> entityList,
            object extraData = null)
        {
            if (null == entityList)
            {
                return(sheet);
            }
            var configuration            = InternalHelper.GetExcelConfigurationMapping <TEntity>();
            var propertyColumnDictionary = InternalHelper.GetPropertyColumnDictionary(configuration);

            var globalDictionary = extraData.ParseParamInfo()
                                   .ToDictionary(x => TemplateOptions.TemplateGlobalParamFormat.FormatWith(x.Key), x => x.Value);

            foreach (var propertyConfiguration in propertyColumnDictionary)
            {
                globalDictionary.Add(TemplateOptions.TemplateHeaderParamFormat.FormatWith(propertyConfiguration.Key.Name), propertyConfiguration.Value.ColumnTitle);
            }

            var dataFuncDictionary = propertyColumnDictionary
                                     .ToDictionary(x => TemplateOptions.TemplateDataParamFormat.FormatWith(x.Key.Name), x => x.Key.GetValueGetter <TEntity>());

            foreach (var key in propertyColumnDictionary.Keys)
            {
                if (InternalCache.OutputFormatterFuncCache.TryGetValue(key, out var formatterFunc) && formatterFunc?.Method != null)
                {
                    dataFuncDictionary[TemplateOptions.TemplateDataParamFormat.FormatWith(key.Name)] = entity =>
                    {
                        var val = key.GetValueGetter <TEntity>()?.Invoke(entity);
                        try
                        {
                            var formattedValue = formatterFunc.Invoke(new[] { entity, val });
                            return(formattedValue);
                        }
                        catch (Exception e)
                        {
                            Debug.WriteLine(e);
                            InvokeHelper.OnInvokeException?.Invoke(e);
                        }
                        return(val);
                    };
                }
            }

            // parseTemplate
            int dataStartRow = -1, dataRowsCount = 0;

            for (var rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null)
                {
                    continue;
                }
                for (var cellIndex = row.FirstCellNum; cellIndex < row.LastCellNum; cellIndex++)
                {
                    var cell = row.GetCell(cellIndex);
                    if (cell == null)
                    {
                        continue;
                    }

                    var cellValue = cell.GetCellValue <string>();
                    if (!string.IsNullOrEmpty(cellValue))
                    {
                        var beforeValue = cellValue;
                        if (dataStartRow <= 0 || dataRowsCount <= 0)
                        {
                            if (dataStartRow >= 0)
                            {
                                if (cellValue.Contains(TemplateOptions.TemplateDataEnd))
                                {
                                    dataRowsCount = rowIndex - dataStartRow + 1;
                                    cellValue     = cellValue.Replace(TemplateOptions.TemplateDataEnd, string.Empty);
                                }
                            }
                            else
                            {
                                if (cellValue.Contains(TemplateOptions.TemplateDataBegin))
                                {
                                    dataStartRow = rowIndex;
                                    cellValue    = cellValue.Replace(TemplateOptions.TemplateDataBegin, string.Empty);
                                }
                            }
                        }

                        foreach (var param in globalDictionary.Keys)
                        {
                            if (cellValue.Contains(param))
                            {
                                cellValue = cellValue
                                            .Replace(param,
                                                     globalDictionary[param]?.ToString() ?? string.Empty);
                            }
                        }

                        if (beforeValue != cellValue)
                        {
                            cell.SetCellValue(cellValue);
                        }
                    }
                }
            }

            if (dataStartRow >= 0 && dataRowsCount > 0)
            {
                foreach (var entity in entityList)
                {
                    sheet.ShiftRows(dataStartRow, sheet.LastRowNum, dataRowsCount);
                    for (var i = 0; i < dataRowsCount; i++)
                    {
                        var row = sheet.CopyRow(dataStartRow + dataRowsCount + i, dataStartRow + i);
                        if (null != row)
                        {
                            for (var j = 0; j < row.LastCellNum; j++)
                            {
                                var cell = row.GetCell(j);
                                if (null != cell)
                                {
                                    var cellValue = cell.GetCellValue <string>();
                                    if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains(TemplateOptions.TemplateDataPrefix))
                                    {
                                        var beforeValue = cellValue;

                                        foreach (var param in dataFuncDictionary.Keys)
                                        {
                                            if (cellValue.Contains(param))
                                            {
                                                cellValue = cellValue.Replace(param,
                                                                              dataFuncDictionary[param]?.Invoke(entity)?.ToString() ?? string.Empty);
                                            }
                                        }

                                        if (beforeValue != cellValue)
                                        {
                                            cell.SetCellValue(cellValue);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    //
                    dataStartRow += dataRowsCount;
                }

                // remove data template
                for (var i = 0; i < dataRowsCount; i++)
                {
                    var row = sheet.GetRow(dataStartRow + i);
                    if (null != row)
                    {
                        sheet.RemoveRow(row);
                    }
                }
                sheet.ShiftRows(dataStartRow + dataRowsCount, sheet.LastRowNum, -dataRowsCount);
            }

            return(sheet);
        }
Пример #19
0
        /// <summary>
        /// 复制单元格(解决xssf处理excel2007复制行=null的bug)
        /// </summary>
        /// <param name="sheet">表单</param>
        /// <param name="sourceIndex">原始行</param>
        /// <param name="targetIndex">目标(起始)行</param>
        /// <param name="count">复制次数</param>
        /// <param name="dataIncluded">是否复制数据</param>
        /// <returns>复制行数</returns>
        public static int CopyRow(ISheet sheet, int sourceIndex, int targetIndex, int count = 1, bool dataIncluded = true)
        {
            if (count > 0)
            {
                IDictionary <int, CellRangeAddress> rowMergeDic = GetRangeBetweenRows(sheet, sourceIndex, targetIndex);

                IRow styleRow = sheet.GetRow(sourceIndex);
                //模板无效时直接退出
                if (styleRow == null)
                {
                    return(0);
                }

                //NPOI的Copy操作默认是复制数据的,如不需要复制数据,用字符串清空
                if (!dataIncluded)
                {
                    foreach (var exCell in styleRow.Cells)
                    {
                        exCell.SetCellValue("");
                    }
                }

                /***
                 * ISheet.CopyRow 只复制不插入(字体、边框、样式、数据、单元格合并,不包括数据有效性)
                 */
                if (sheet is HSSFSheet)
                {
                    if (sheet.GetRow(targetIndex) == null)
                    {
                        sheet.CreateRow(targetIndex);//达到每次Copy都插入行的目标
                    }
                    for (int indexOffset = 0; indexOffset < count; indexOffset++)
                    {
                        //已经存在的行,自动插入新行;不存在的行,直接Copy行
                        sheet.CopyRow(sourceIndex, targetIndex);
                        //XSSF的此操作会导致targetIndex变成空行
                    }
                }
                else if (sheet is XSSFSheet)
                {
                    if (targetIndex <= sheet.LastRowNum)
                    {
                        sheet.ShiftRows(targetIndex, sheet.LastRowNum, count, true, false);//目的:让目标行始终是空的
                    }
                    for (int indexOffset = 0; indexOffset < count; indexOffset++)
                    {
                        //目标行:不存在时,直接Copy;存在时,有bug!执行后目标行反而变成了空行
                        sheet.CopyRow(sourceIndex, targetIndex + indexOffset);
                    }
                }
                CopyFormula(styleRow, targetIndex, count);

                CellRangeAddress tmpRange = null;
                foreach (var keyValue in rowMergeDic)
                {
                    tmpRange          = keyValue.Value;
                    tmpRange.LastRow += count;
                    sheet.RemoveMergedRegion(keyValue.Key);
                    sheet.AddMergedRegion(tmpRange);
                }
                return(count);
            }
            return(0);
        }
Пример #20
0
        public void WriteSchemaInfo(ISheet formatSheet, ISheet sheet, EPoint start, SchemaInfoTuple schemaInfo)
        {
            //复制表头
            formatSheet.CopyRow(ExcelTemplateFormat.TSTHeadRowNum, sheet, start.X, start.Y);
            //写入 表名(说明) 信息
            String name = schemaInfo.ObjectSchema.Name;

            if (!String.IsNullOrEmpty(schemaInfo.ObjectSchema.Explain))
            {
                name += $"({schemaInfo.ObjectSchema.Explain})";
            }
            sheet.SetCellValue(start.X, 0, name);

            start.X += ExcelTemplateFormat.RowSpan;
            //复制列头
            formatSheet.CopyRow(ExcelTemplateFormat.TSTColumnHeaderRowNum, sheet, start.X, start.Y);
            start.X += ExcelTemplateFormat.RowSpan;



            //开始写入列信息
            Int32 num = 1;

            if (schemaInfo.ColumnSchemas == null || schemaInfo.ColumnSchemas.Count == 0)
            {
                return;
            }
            foreach (var colSchema in schemaInfo.ColumnSchemas)
            {
                //复制行
                formatSheet.CopyRow(ExcelTemplateFormat.TSTRowTemlateNum, sheet, start.X, start.Y);

                Int32 col = 0;
                //写入序号
                sheet.SetCellValue(start.X, col, num++);
                col += ExcelTemplateFormat.TableNumColLength;

                //写入列名
                sheet.SetCellValue(start.X, col, colSchema.Name);
                col += ExcelTemplateFormat.TableFieldNameColLength;

                //写入注释
                sheet.SetCellValue(start.X, col, colSchema.Explain);
                col += ExcelTemplateFormat.TableExplainColLength;

                //写入类型
                sheet.SetCellValue(start.X, col, colSchema.DbTypeString);
                col += ExcelTemplateFormat.TableTypeColLength;

                //写入长度
                if (colSchema.Length.HasValue)
                {
                    sheet.SetCellValue(start.X, col, colSchema.Length.Value);
                }

                col += ExcelTemplateFormat.TableLengthColLength;

                //写入主键信息
                if (colSchema.IsPrimaryKey)
                {
                    sheet.SetCellValue(start.X, col, colSchema.IsPrimaryKey);
                }

                col += ExcelTemplateFormat.TableIsPrimaryColLength;

                //写入可空信息
                if (!colSchema.IsNullable)
                {
                    sheet.SetCellValue(start.X, col, colSchema.IsNullable);
                }
                col += ExcelTemplateFormat.TableIsNullableColLength;


                start.X += ExcelTemplateFormat.RowSpan;
            }
        }
Пример #21
0
        public void FillChartData(int chartIndex, DataTable dt, int categoryTextLength)
        {
            var          chart  = document.Charts[chartIndex];
            var          series = chart.Series;
            MemoryStream stream = new MemoryStream();

            stream.Write(chart.ExcelFileBytes, 0, chart.ExcelFileBytes.Length);
            stream.Position = 0;
            IWorkbook book       = new XSSFWorkbook(stream);
            ISheet    sheet      = book.GetSheetAt(0);
            int       lastRowNum = sheet.LastRowNum;
            int       lastColNum = sheet.GetRow(0).LastCellNum;
            Dictionary <string, List <string> > seriesData = new Dictionary <string, List <string> >();

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                seriesData.Add(j.ToString(), new List <string>());
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr  = dt.Rows[i];
                IRow    row = sheet.GetRow(i + 1);
                if (row == null)
                {
                    row = sheet.CopyRow(1, i + 1);
                }
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    switch (dt.Columns[j].DataType.FullName)
                    {
                    case "System.Double":
                        row.Cells[j].SetCellValue(Convert.ToDouble(dr[j]));
                        break;

                    default:
                        row.Cells[j].SetCellValue(dr[j].ToString());
                        break;
                    }
                    var list = seriesData[j.ToString()];
                    if (j == 0 && categoryTextLength > 0)
                    {
                        if (dr[j].ToString().Length > categoryTextLength)
                        {
                            list.Add(dr[j].ToString().Substring(0, categoryTextLength) + "..");
                        }
                        else
                        {
                            list.Add(dr[j].ToString());
                        }
                    }
                    else
                    {
                        list.Add(dr[j].ToString());
                    }
                }
            }
            int seriesIndex = 1;

            foreach (var item in series)
            {
                item.Bind(seriesData["0"], seriesData[seriesIndex.ToString()]);
                seriesIndex++;
            }
            stream.Close();
            stream.Dispose();

            //转为字节数组
            stream = new MemoryStream();
            book.Write(stream);
            chart.ExcelFileBytes = stream.ToArray();
            stream.Close();
            stream.Dispose();
        }
Пример #22
0
        private void Store(ISheet sheet, IPersistEntity entity, ClassMapping mapping, ExpressType expType, EntityContext context)
        {
            var             multiRow     = -1;
            List <string>   multiValues  = null;
            PropertyMapping multiMapping = null;
            var             row          = GetRow(sheet);

            //fix on "Special Case" Assembly Row to Entity mapping
            if ((context?.RootEntity != null) && (expType?.ExpressNameUpper == "TYPEORCOMPONENT"))  //without CobieExpress reference and not using reflection this is as good as it gets to ID Assembly
            {
                RowNoToEntityLabelLookup[sheet.SheetName].Add(row.RowNum, context.RootEntity.EntityLabel);
            }
            else
            {
                RowNoToEntityLabelLookup[sheet.SheetName].Add(row.RowNum, entity.EntityLabel);
            }

            foreach (var propertyMapping in mapping.PropertyMappings)
            {
                object value = null;
                foreach (var path in propertyMapping.Paths)
                {
                    value = GetValue(entity, expType, path, context);
                    if (value != null)
                    {
                        break;
                    }
                }
                if (value == null && propertyMapping.Status == DataStatus.Required)
                {
                    value = propertyMapping.DefaultValue ?? "n/a";
                }

                var isMultiRow = IsMultiRow(value, propertyMapping);
                if (isMultiRow)
                {
                    multiRow = row.RowNum;
                    var values     = new List <string>();
                    var enumerable = value as IEnumerable <string>;
                    if (enumerable != null)
                    {
                        values.AddRange(enumerable);
                    }

                    //get only first value and store it
                    var first = values.First();
                    Store(row, first, propertyMapping);

                    //set the rest for the processing as multiValue
                    values.Remove(first);
                    multiValues  = values;
                    multiMapping = propertyMapping;
                }
                else
                {
                    Store(row, value, propertyMapping);
                }
            }

            //adjust width of the columns after the first and the eight row
            //adjusting fully populated workbook takes ages. This should be almost all right
            if (row.RowNum == 1 || row.RowNum == 8)
            {
                AdjustAllColumns(sheet, mapping);
            }

            //it is not a multi row so return
            if (multiRow <= 0 || multiValues == null || !multiValues.Any())
            {
                return;
            }

            //add repeated rows if necessary
            foreach (var value in multiValues)
            {
                var rowNum = GetNextRowNum(sheet);
                var copy   = sheet.CopyRow(multiRow, rowNum);
                Store(copy, value, multiMapping);
                RowNoToEntityLabelLookup[sheet.SheetName].Add(rowNum, entity.EntityLabel);
            }
        }
Пример #23
0
        public static bool  WriteExcel(string path, string templatePath, DataTable dt, int startRow, int startCol)
        {
            string pLocalFilePath = templatePath;                          //要复制的文件路径
            string pSaveFilePath  = path;                                  //指定存储的路径

            if (File.Exists(pLocalFilePath) && !path.Equals(templatePath)) //必须判断要复制的文件是否存在
            {
                File.Copy(pLocalFilePath, pSaveFilePath, true);            //三个参数分别是源文件路径,存储路径,若存储路径有相同文件是否替换
            }
            IWorkbook workbook = null;

            try
            {
                RemoveEmpty(ref dt);
                workbook = WorkbookFactory.Create(pSaveFilePath);
                ISheet     sheet = workbook.GetSheetAt(0);
                IRow       row   = sheet.GetRow(startRow);
                ICellStyle style = workbook.CreateCellStyle();
                style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin;
                int end = 0;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.Cells[j + 1].SetCellValue(dt.Columns[j].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (sheet.GetRow(i + startRow + 1) == null)
                    {
                        sheet.CopyRow(startRow, i + startRow + 1);
                    }
                    row = sheet.GetRow(i + startRow + 1);
                    DataRow dr   = dt.Rows[i];
                    ICell   cell = row.GetCell(0);
                    //cell.CellStyle = style;
                    end = i + startRow + 1;
                    if (cell == null)
                    {
                        cell = row.CreateCell(0);
                    }
                    cell.SetCellValue((i + 1).ToString());
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (row.GetCell(j + startCol) == null)
                        {
                            cell           = row.CreateCell(j + startCol);
                            cell.CellStyle = style;
                        }
                        else
                        {
                            cell = row.GetCell(j + startCol);
                        }
                        cell.SetCellValue(dr[dt.Columns[j]].ToString());
                    }
                }
                //导出excel
                FileStream fs = new FileStream(pSaveFilePath, FileMode.Create, FileAccess.ReadWrite);
                workbook.Write(fs);
                fs.Close();
            }
            catch (Exception e) {
                if (workbook != null)
                {
                    workbook.Close();
                }
                MessageBox.Show(e.Message);
                return(false);
            }
            if (workbook != null)
            {
                workbook.Close();
            }
            return(true);
        }
Пример #24
0
        /// <summary>
        /// 供应商客服提取报增信息
        /// </summary>
        /// <param name="getParam"></param>
        /// <returns></returns>
        public Common.ClientResult.UrlResult SupplierExport([FromBody] GetDataParam getParam)
        {
            FileStream   file     = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/供应商客服报增数据提取模板.xls"), FileMode.Open, FileAccess.Read);
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            //ICellStyle cellStyle = workbook.CreateCellStyle();
            //cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            //cellStyle.FillPattern = FillPattern.SolidForeground;

            ICellStyle style = workbook.CreateCellStyle();

            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
            style.FillPattern         = FillPattern.SolidForeground;

            try
            {
                string search = getParam.search;
                int    total  = 0;

                Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
                if (queryDic != null && queryDic.Count > 0)
                {
                    if (queryDic.ContainsKey("CollectState") && !string.IsNullOrWhiteSpace(queryDic["CollectState"]))
                    {
                        string str = queryDic["CollectState"];
                        if (str.Equals(Common.CollectState.未提取.ToString()))
                        {
                            string state = Common.EmployeeAdd_State.待供应商客服提取.ToString();
                            search += "State&" + state + "^";
                        }
                        else if (str.Equals(Common.CollectState.已提取.ToString()))
                        {
                            string state = Common.EmployeeAdd_State.已发送供应商.ToString();
                            search += "State&" + state + "^";
                        }
                    }
                }
                search += "UserID_Supplier&" + LoginInfo.UserID + "^";
                //search += "BranchID&" + LoginInfo.BranchID + "^";
                //search += "BranchName&" + LoginInfo.BranchName + "^";


                List <SupplierAddView> queryData = m_BLL.GetEmployeeAddExcelListBySupplier(search).OrderBy(c => c.CertificateNumber).ThenBy(c => c.CityID).ThenBy(c => c.OperationTime).ToList();
                if (queryData.Count == 0)
                {
                    var data = new Common.ClientResult.UrlResult
                    {
                        Code    = ClientCode.FindNull,
                        Message = "没有符合条件的数据",
                        URL     = ""
                    };
                    return(data);
                }

                using (MemoryStream ms = new MemoryStream())
                {
                    #region 生成Excel


                    string ids      = string.Empty;
                    int[]  intArray = new int[queryData.Count];

                    //员工社保一览
                    ISheet sheet = workbook.GetSheetAt(0);
                    sheet.SetActiveCell(0, 0);
                    int rowNum = 1;

                    string tmpCertificateNumber = "";
                    string tmpCity      = "";
                    string tmpYearMonth = "";
                    string strRemark    = "";

                    IRow currentRow = null;

                    #region 数据输出
                    int i = 0;
                    foreach (var query in queryData)
                    {
                        intArray[i] = query.EmployeeAddId ?? 0;
                        i++;
                        if (tmpCertificateNumber != query.CertificateNumber || tmpCity != query.City ||
                            (tmpYearMonth != Convert.ToDateTime(query.OperationTime).ToString("yyyyMM") && query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金))
                        {
                            //保存临时数据
                            tmpCertificateNumber = query.CertificateNumber;
                            tmpCity      = query.City;
                            tmpYearMonth = Convert.ToDateTime(query.OperationTime).ToString("yyyyMM");

                            if (currentRow != null)
                            {
                                currentRow.GetCell(19).SetCellValue(strRemark);  // 备注
                                strRemark = "";
                            }

                            rowNum++;

                            currentRow = sheet.CopyRow(rowNum + 1, rowNum);

                            currentRow.GetCell(0).SetCellValue(rowNum - 1);              // 序号
                            currentRow.GetCell(1).SetCellValue(query.CustomerName);      // 客服姓名
                            currentRow.GetCell(2).SetCellValue(query.City);              // 缴费地
                            currentRow.GetCell(3).SetCellValue(query.BranchName);        // 分公司名称
                            currentRow.GetCell(4).SetCellValue(query.CompanyName);       // 客户单位名称
                            currentRow.GetCell(5).SetCellValue(query.EmployeeName);      // 员工姓名
                            currentRow.GetCell(6).SetCellValue(query.CertificateNumber); // 身份证号码
                            currentRow.GetCell(7).SetCellValue(query.Telephone);         // 联系电话
                            if (query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金)
                            {
                                currentRow.GetCell(8).SetCellValue(tmpYearMonth);  // 缴费开始时间
                            }
                        }
                        int cellNum = 0;
                        switch (Convert.ToInt32(query.InsuranceKindId))
                        {
                        case (int)Common.EmployeeAdd_InsuranceKindId.公积金:
                            currentRow.GetCell(15).SetCellValue(Convert.ToDateTime(query.OperationTime).ToString("yyyyMM")); // 公积金缴费开始时间
                            currentRow.GetCell(17).SetCellValue((query.EmployeePercent * 100).ToString() + "%");             // 公积金比例(个人)
                            currentRow.GetCell(18).SetCellValue((query.CompanyPercent * 100).ToString() + "%");              // 公积金比例(单位)

                            cellNum = 16;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.养老:
                            cellNum = 9;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.医疗:
                            cellNum = 10;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.失业:
                            cellNum = 11;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.工伤:
                            cellNum = 12;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.生育:
                            cellNum = 13;
                            break;

                        case (int)Common.EmployeeAdd_InsuranceKindId.大病:
                            cellNum = 14;
                            break;

                        default:
                            break;
                        }
                        currentRow.GetCell(cellNum).SetCellValue(query.CompanyNumber.ToString());  // 基数
                        if (query.State == "1")
                        {
                            currentRow.GetCell(cellNum).CellStyle = style;
                        }

                        if (!string.IsNullOrEmpty(query.SupplierRemark))
                        {
                            strRemark += query.SupplierRemark + "  ";
                        }
                    }

                    if (!string.IsNullOrEmpty(strRemark))
                    {
                        currentRow.GetCell(19).SetCellValue(strRemark);  // 备注
                    }

                    #endregion

                    #endregion

                    bool rst = m_BLL.ChangeStatus(intArray, Common.EmployeeAdd_State.待供应商客服提取.ToString(), EmployeeAdd_State.已发送供应商.ToString(), LoginInfo.LoginName);

                    if (rst)
                    {
                        string fileName = "供应商客服导出_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
                        //string fileName = name + "供应商导出.xls";
                        string urlPath  = "DataExport\\" + fileName;                                     // 文件下载的URL地址,供给前台下载
                        string filePath = System.Web.HttpContext.Current.Server.MapPath("\\") + urlPath; // 文件路径
                        file = new FileStream(filePath, FileMode.Create);
                        workbook.Write(file);
                        file.Close();

                        string Message = "已成功提取报增信息";

                        return(new Common.ClientResult.UrlResult
                        {
                            Code = ClientCode.Succeed,
                            Message = "已成功提取报增信息",
                            URL = urlPath
                        });
                    }
                    else
                    {
                        return(new Common.ClientResult.UrlResult
                        {
                            Code = ClientCode.Succeed,
                            Message = "报增信息提取失败",
                            URL = ""
                        });
                    }
                }
            }
            //catch (Exception e)
            //{
            //    file.Close();
            //    return new Common.ClientResult.UrlResult
            //    {
            //        Code = ClientCode.Fail,
            //        Message = e.Message
            //    };
            //}
            catch (DbEntityValidationException ex)
            {
                string errmsg = "";
                foreach (var errors in ex.EntityValidationErrors)
                {
                    foreach (var item in errors.ValidationErrors)
                    {
                        errmsg += item.ErrorMessage + item.PropertyName;
                    }
                }
                return(null);
            }
        }
Пример #25
0
        public override void SetValue(ISheet sheet, JToken token)
        {
            JArray data;
            if (this.Each == "$root")
            {
                data = (JArray)token;
            }
            else
            {
                data = (JArray)token[Each];
            }
            var row = sheet.GetRow(RowIndex);

            var grouprows = ExcelGroupRow.GetGroupRows(data, TreeCode);

            for (int i = 0; i < data.Count; i++)
            {
                var item = data[i];
                foreach (var cellTemplate in Cells)
                {
                    var cell = row.GetCell(cellTemplate.ColIndex);
                    var val = item[cellTemplate.Bind];

                        cellTemplate.SetCellValue(cell, val);
                }
                if (i != data.Count - 1) {
                    row = sheet.CopyRow(row.RowNum, row.RowNum + 1);
                }
            }

            var groups = grouprows.Values.OrderByDescending(o => o.TreeCode).ToList();
            var b = JsonConvert.SerializeObject(groups);
            foreach (var grouprow in groups)
            {

                sheet.GroupRow(grouprow.StartIndex + RowIndex+1, grouprow.EndIndex + RowIndex);
            }
            if (data.Count == 0) {
                sheet.ShiftRows(row.RowNum+1, sheet.LastRowNum, -1);
            }
        }