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);
}
}
