private static List <CollectXZQ> GetCollectXZQ()
{
var list = new List <CollectXZQ>();
var nodes = XmlManager.GetList("/Tables/Citys/Citys", XmlEnum.Field);
if (nodes != null && nodes.Count > 0)
{
for (var i = 0; i < nodes.Count; i++)
{
var node = nodes[i];
var model = new CollectXZQ
{
XZQDM = node.Attributes["Code"].Value,
XZQMC = node.Attributes["Name"].Value
};
var children = node.SelectNodes("City");
if (children != null && children.Count > 0)
{
var result = new List <XZC>();
for (var j = 0; j < children.Count; j++)
{
var child = children[j];
var entry = new XZC
{
XZCDM = child.Attributes["Code"].Value,
XZCMC = child.Attributes["Name"].Value
};
result.Add(entry);
}
model.Children = result.OrderBy(e => e.XZCDM).ToList();
}
list.Add(model);
}
}
return(list.OrderBy(e => e.XZQDM).ToList());
}
private void Write()
{
IWorkbook workbook = ModelFilePath.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;
var all = new List <FieldValue>();
var sumA = 0;
sheet.ShiftRows(index + 1, index + 5, CollectXZQ.Count + CollectXZQ.Sum(e => e.Children.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);
//sheet.ShiftRows(index + 1, index + 5, 1);
if (shi.Children != null)
{
var i = index;
//sheet.ShiftRows(index + 2, index + 6, shi.Children.Count);
var sumList = new List <FieldValue>();
var sum1 = 0;
#region 写入市下面的区县信息数据
foreach (var quxian in shi.Children)
{
row = sheet.GetRow(index) ?? sheet.CreateRow(index);
row.Height = modelRow.Height;
cell = ExcelClass.GetCell(row, 2, modelRow);
#region 边框
ExcelClass.GetCell(row, 0, modelRow);
ExcelClass.GetCell(row, 1, modelRow);
#endregion
cell.SetCellValue(quxian.XZCDM);
ExcelClass.GetCell(row, 3, modelRow).SetCellValue(quxian.XZCMC);
var entry = Result.FirstOrDefault(e => e.XZQDM.ToLower() == quxian.XZCDM.ToLower() && e.XZQMC.ToLower() == quxian.XZCMC.ToLower());
if (entry != null)
{
cell = ExcelClass.GetCell(row, 4, modelRow);
cell.SetCellValue(entry.Count);
sum1 += entry.Count;
foreach (var field in entry.Values)
{
cell = ExcelClass.GetCell(row, CollectTable.CollectIndex + field.Index, modelRow);
cell.SetCellValue(field.Value);
}
sumList.AddRange(entry.Values);
}
index++;
}
#endregion
row = sheet.GetRow(index) ?? sheet.CreateRow(index);
row.Height = modelRow.Height;
cell = ExcelClass.GetCell(row, 4, modelRow);
cell.SetCellValue(sum1);
ExcelClass.GetCell(row, 0, modelRow);
ExcelClass.GetCell(row, 1, modelRow);
ExcelClass.GetCell(row, 2, modelRow).SetCellValue("小计");
ExcelClass.GetCell(row, 3, modelRow);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(index, index, 2, 3));
foreach (var field in Fields)
{
cell = ExcelClass.GetCell(row, field.Index + CollectTable.CollectIndex, 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
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, index, 0, 0));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, index, 1, 1));
all.AddRange(sumList);
sumA += sum1;
}
index++;
}
#region 写合计
row = sheet.GetRow(index) ?? sheet.CreateRow(index);
row.Height = modelRow.Height;
ExcelClass.GetCell(row, 4, modelRow).SetCellValue(sumA);
foreach (var field in Fields)
{
cell = ExcelClass.GetCell(row, field.Index + CollectTable.CollectIndex, 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(SaveFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
}
private void Write1()
{
IWorkbook workbook = ModelFilePath.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;
sheet.ShiftRows(index + 1, index + 5, CollectXZQ.Count + CollectXZQ.Sum(e => e.Children.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)
{
var start = index;
#region 写入每个县区数值
var sumList = new List <FieldValue>();
var sum1 = 0;
foreach (var quxian in shi.Children)
{
row = sheet.GetRow(index) ?? sheet.CreateRow(index);
row.Height = modelRow.Height;
ExcelClass.GetCell(row, 0, modelRow);
ExcelClass.GetCell(row, 1, modelRow);
cell = ExcelClass.GetCell(row, 2, modelRow);
cell.SetCellValue(quxian.XZCDM); //县（区）（市）代码
ExcelClass.GetCell(row, 3, modelRow).SetCellValue(quxian.XZCMC); //县（区）（市）名称
var values = Search(quxian.XZCDM, quxian.XZCMC);
if (values != null)
{
if (Flag)
{
cell = ExcelClass.GetCell(row, 4, modelRow);
cell.SetCellValue(values.Count);
sum1 += values.Count;
}
foreach (var field in Fields)
{
cell = ExcelClass.GetCell(row, Flag? CollectTable.CollectIndex + field.Index:field.Index, modelRow);
var list = values.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
}
sumList.AddRange(values);
}
index++;
}
row = sheet.GetRow(index) ?? sheet.CreateRow(index);
row.Height = modelRow.Height;
#region 写入地市小计
ExcelClass.GetCell(row, 2, modelRow).SetCellValue("小计");
ExcelClass.GetCell(row, 0, modelRow);
ExcelClass.GetCell(row, 1, modelRow);
if (Flag)
{
cell = ExcelClass.GetCell(row, 4, modelRow);
cell.SetCellValue(sum1);
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(index, index, 2, 3));
foreach (var field in Fields)
{
cell = ExcelClass.GetCell(row, Flag? field.Index + CollectTable.CollectIndex:field.Index, 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
}
#endregion
all.AddRange(sumList);
sumA += sum1;
#endregion
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(start, index, 0, 0));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(start, index, 1, 1));
}
index++;
}
#region 合计
row = sheet.GetRow(index) ?? sheet.CreateRow(index);
row.Height = modelRow.Height;
if (Flag)
{
ExcelClass.GetCell(row, 4, modelRow).SetCellValue(sumA);
}
foreach (var field in Fields)
{
cell = ExcelClass.GetCell(row, Flag? field.Index + CollectTable.CollectIndex:field.Index, 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(SaveFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
}
