private void Write2() { IWorkbook workbook = ModelFilePath2.OpenExcel(); if (workbook != null) { ISheet sheet = workbook.GetSheetAt(0); if (sheet != null) { IRow modelRow = sheet.GetRow(CollectTable.StartIndex); IRow row = null; ICell cell = null; var index = CollectTable.StartIndex; sheet.ShiftRows(index + 1, index + 5, CollectXZQ.Count - 1); var AllList = new List <FieldValue>(); var all = 0; var two = 0; foreach (var shi in CollectXZQ) { row = sheet.GetRow(index) ?? sheet.CreateRow(index); row.Height = modelRow.Height; cell = ExcelClass.GetCell(row, 0, modelRow); cell.SetCellValue(shi.XZQDM); ExcelClass.GetCell(row, 1, modelRow).SetCellValue(shi.XZQMC); #region if (shi.Children != null) { cell = ExcelClass.GetCell(row, 2, modelRow); cell.SetCellValue(shi.Children.Count); two += shi.Children.Count; var sumlist = new List <FieldValue>(); var sum1 = 0; foreach (var quxian in shi.Children) { var entry = Result.FirstOrDefault(e => e.XZQDM.ToLower() == quxian.XZCDM.ToLower() && e.XZQMC.ToLower() == quxian.XZCMC.ToLower()); if (entry != null) { sum1 += entry.Count; sumlist.AddRange(entry.Values); } } ExcelClass.GetCell(row, 3, modelRow).SetCellValue(sum1); foreach (var field in Fields) { cell = ExcelClass.GetCell(row, field.Index + CollectTable.CollectIndex - 1, modelRow); var list = sumlist.Where(e => e.Index == field.Index).ToList(); #region 写入市 小计值 if (field.Type == ExcelType.Double) { var a = .0; var sum = .0; foreach (var item in list) { if (double.TryParse(item.Value, out a)) { sum += Math.Round(a, 4); } } cell.SetCellValue(sum); } else if (field.Type == ExcelType.Int) { var b = 0; var sum = 0; foreach (var item in list) { if (int.TryParse(item.Value, out b)) { sum += b; } } cell.SetCellValue(sum); } #endregion } AllList.AddRange(sumlist); all += sum1; } #endregion index++; } #region 写合计 row = sheet.GetRow(index) ?? sheet.CreateRow(index); row.Height = modelRow.Height; ExcelClass.GetCell(row, 2, modelRow).SetCellValue(two); ExcelClass.GetCell(row, 3, modelRow).SetCellValue(all); foreach (var field in Fields) { cell = ExcelClass.GetCell(row, field.Index + CollectTable.CollectIndex - 1, modelRow); var list = AllList.Where(e => e.Index == field.Index).ToList(); if (field.Type == ExcelType.Double) { var a = .0; var sum = .0; foreach (var item in list) { if (double.TryParse(item.Value, out a)) { sum += Math.Round(a, 4); } } cell.SetCellValue(sum); } else if (field.Type == ExcelType.Int) { var b = 0; var sum = 0; foreach (var item in list) { if (int.TryParse(item.Value, out b)) { sum += b; } } cell.SetCellValue(sum); } } #endregion } } using (var fs = new FileStream(SaveFilePath2, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { workbook.Write(fs); } }
private void Write2() { IWorkbook workbook = ModelFilePath2.OpenExcel(); if (workbook != null) { ISheet sheet = workbook.GetSheetAt(0); if (sheet != null) { IRow modelRow = sheet.GetRow(CollectTable.StartIndex); var header = sheet.GetRow(0); ExcelClass.GetCell(header, 0).SetCellValue(CollectTable.Title); IRow row = null; ICell cell = null; var index = CollectTable.StartIndex; var all = new List <FieldValue>(); var sumA = 0; var two = 0; sheet.ShiftRows(index + 1, index + 5, CollectXZQ.Count - 1); foreach (var shi in CollectXZQ) { row = sheet.GetRow(index) ?? sheet.CreateRow(index); row.Height = modelRow.Height; cell = ExcelClass.GetCell(row, 0, modelRow); cell.SetCellValue(shi.XZQDM); ExcelClass.GetCell(row, 1, modelRow).SetCellValue(shi.XZQMC); if (shi.Children != null) { if (Flag) { ExcelClass.GetCell(row, 2, modelRow).SetCellValue(shi.Children.Count); } two += shi.Children.Count; var sumlist = new List <FieldValue>(); var sum1 = 0; foreach (var quxian in shi.Children) { var values = Search(quxian.XZCDM, quxian.XZCMC); if (values != null) { sum1 += values.Count; sumlist.AddRange(values); } } if (Flag) { ExcelClass.GetCell(row, 3, modelRow).SetCellValue(sum1); } foreach (var field in Fields) { cell = ExcelClass.GetCell(row, Flag? field.Index + CollectTable.CollectIndex - 1:field.Index - 2, modelRow); var list = sumlist.Where(e => e.Index == field.Index).ToList(); #region 写入小计 if (field.Type == ExcelType.Double) { var a = .0; var sum = .0; foreach (var item in list) { if (double.TryParse(item.Value, out a)) { sum += Math.Round(a, 4); } } cell.SetCellValue(sum); } else if (field.Type == ExcelType.Int) { var b = 0; var sum = 0; foreach (var item in list) { if (int.TryParse(item.Value, out b)) { sum += b; } } cell.SetCellValue(sum); } #endregion } all.AddRange(sumlist); sumA += sum1; } index++; } #region 写总计 row = sheet.GetRow(index) ?? sheet.CreateRow(index); row.Height = modelRow.Height; if (Flag) { ExcelClass.GetCell(row, 2, modelRow).SetCellValue(two); ExcelClass.GetCell(row, 3, modelRow).SetCellValue(sumA); } foreach (var field in Fields) { cell = ExcelClass.GetCell(row, Flag? field.Index + CollectTable.CollectIndex - 1:field.Index - 2, modelRow); var list = all.Where(e => e.Index == field.Index).ToList(); if (field.Type == ExcelType.Double) { var a = .0; var sum = .0; foreach (var item in list) { if (double.TryParse(item.Value, out a)) { sum += Math.Round(a, 4); } } cell.SetCellValue(sum); } else if (field.Type == ExcelType.Int) { var b = 0; var sum = 0; foreach (var item in list) { if (int.TryParse(item.Value, out b)) { sum += b; } } cell.SetCellValue(sum); } } #endregion } } using (var fs = new FileStream(SaveFilePath2, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { workbook.Write(fs); } }