private void fileBrowser1_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
{
if (null == FieldsMapper)
{
return;
}
var filedlg = new OpenFileDialog();
filedlg.Filter = this.FileFilter;
if (filedlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
fileBrowser1.Text = filedlg.FileName;
//读取文件数据
var dlg = new DevExpress.Utils.WaitDialogForm("正在加载数据,请稍候...", "导入");
try {
var ipitm = ExcelCommon.ReadXls <t_item>(fileBrowser1.Text, FieldsMapper);
this.GridControl.DataSource = ipitm;
} catch (Exception) {
throw;
} finally {
dlg.Close();
}
}
}
protected override void AnalyseSetting()
{
var cellValue = this.Content;
var arry = cellValue.Split(new char[2] {
'{', '}'
}, StringSplitOptions.RemoveEmptyEntries);
if (arry.Length == 0)
{
return;
}
foreach (var item in arry)
{
if (item.IndexOf(":") > -1 && item.IndexOf(";") > -1)
{
var settingItemArry = item.Split(new char[1] {
';'
}, StringSplitOptions.RemoveEmptyEntries);
if (settingItemArry.Length == 0)
{
continue;
}
foreach (var arryItem in settingItemArry)
{
var settingItem = arryItem.Split(':');
if (settingItem.Length < 2)
{
continue;
}
var key = settingItem[0];
var value = settingItem[1];
if (string.IsNullOrEmpty(key))
{
continue;
}
switch (key.ToUpper())
{
case "DATASOURCE": { this.DataSource = value.ToUpper(); } break;
case "ADDRESSLEFTTOP": { this.AddressLeftTop = value.ToUpper(); } break;
case "ADDRESSRIGHTBOTTOM": { this.AddressRightBottom = value.ToUpper(); } break;
}
}
}
//var cellValueStr = cell.Value?.ToString() ?? "";
//cell.Value = cellValueStr.Replace("{" + item + "}", "");
}
ExcelCommon.CalcRowCol(this.AddressLeftTop, out int fromRow, out int fromCol);
ExcelCommon.CalcRowCol(this.AddressRightBottom, out int toRow, out int toCol);
this.FromRow = fromRow;
this.FromCol = fromCol;
this.ToRow = toRow;
this.ToCol = toCol;
this.ScopeRange = this.CurrentCell.Worksheet.Cells[this.FromRow, this.FromCol, this.ToRow, this.ToCol];
}
protected void btnAdvanted_Click(object sender, EventArgs e)
{
BookDemo manager = new BookDemo("Danh sách nhân viên ");
var d = ddlPhongBan.Items;
string mapb = ddlPhongBan.SelectedValue;
string macn = ddlChiNhanh.SelectedValue;
//WorksheetConfig setting = new WorksheetConfig();
List <ExcelCore.ExcelColumnContent> columns = new List <ExcelColumnContent>();
for (int i = 0; i < ckblColumns.Items.Count; i++)
{
if (ckblColumns.Items[i].Selected)
{
columns.Add(new ExcelColumnContent {
Name = ckblColumns.Items[i].Value, Caption = ckblColumns.Items[i].Text
});
}
}
//List<ExcelCore.ExcelColumnContent> columns = new List<ExcelCore.ExcelColumnContent> {
// new ExcelColumnContent{ Width=15, Caption="Mã số",Name="MaNV"},
// new ExcelColumnContent{ Width=25, Caption="Tên nhân viên",Name="TenNV"},
// new ExcelColumnContent{ Width=18,Caption="Ngày sinh",Name="NgaySinh"},
// new ExcelColumnContent{ Width=25,Caption="Địa chỉ",Name="DiaChi"},
// new ExcelColumnContent{ Width=16,Caption="Số điện thoại",Name="DienThoai"},
// new ExcelColumnContent{ Width=15,Caption="CMND",Name="CMND"},
// new ExcelColumnContent{ Width=8,Caption="Điểm",Name="Diem"}
// };
//setting.Title.SubTitle.Text = string.Format("Từ ngày {0} đến {1}", DateTime.Now.AddDays(-30).ToString("dd/MM/yyyy"), DateTime.Now.AddDays(-1).ToString("dd/MM/yyyy"));
//setting.Header.HeaderLeft.Add(new TextComponent { Text = "SỞ Y TẾ HÀ NỘI", Style = styleheader });
//setting.Header.HeaderLeft.Add(new TextComponent { Text = "BỆNH VIỆN ĐA KHOA ĐAN PHƯỢNG", Style = styleHeaderBold });
//setting.Header.HeaderRight.Add(new TextComponent { Text = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM", Style = styleHeaderBold });
//setting.Header.HeaderRight.Add(new TextComponent { Text = "Độc lập - Tự do - Hạnh phúc", Style = styleHeaderBold });
//setting.Title.Title.Style = styletitle;
if (mapb == "All")
{
for (int i = 1; i < d.Count; i++)
{
//WorksheetConfig set = ExcelCommon.DeepCopy<WorksheetConfig>(setting);
WorksheetLayoutLevel1 layout1 = new WorksheetLayoutLevel1();
DataTable dt = new NhanVien().GetByPhongban(d[i].Value);
columns = ExcelCommon.GetColumnsValid(dt, columns);
//columns.ForEach(p => p.Width = 128 / columns.Count);
DataTable dnew = dt.DefaultView.ToTable(true, columns.Select(p => p.Name).ToArray());
layout1.SetName(d[i].Text);
layout1.SetTitle("Danh sách nhân viên " + d[i].Text);
if (i != 1)
{
layout1.SetTitle("Danh sách nhân viên 1 " + d[i].Text);
layout1.Setting.Title.Title.Style.FontSize = 33;
}
layout1.SetDataSource(dnew);
layout1.SetColumns(columns);
manager.AddSheet(layout1);
}
}
else
{
//WorksheetConfig set = ExcelCommon.DeepCopy<WorksheetConfig>(setting);
WorksheetLayoutLevel1 layout1 = new WorksheetLayoutLevel1();
DataTable dt = new NhanVien().GetByPhongban(mapb);
columns = ExcelCommon.GetColumnsValid(dt, columns);
//columns.ForEach(p => p.Width = 128 / columns.Count);
DataTable dnew = dt.DefaultView.ToTable(true, columns.Select(p => p.Name).ToArray());
layout1.SetColumns(columns);
layout1.SetName(ddlPhongBan.SelectedItem.Text);
layout1.SetTitle("Danh sách nhân viên " + ddlPhongBan.SelectedItem.Text);
layout1.SetDataSource(dnew);
manager.AddSheet(layout1);
}
manager.Export();
}
/// <summary>
/// 填充报表(待重构)
/// </summary>
/// <param name="workSheet"></param>
/// <param name="dataSource"></param>
/// <returns></returns>
public ExcelWorksheet FillSheetData(ExcelWorksheet workSheet, ExcelObject dataSource)
{
if (workSheet == null || dataSource == null)
{
return(workSheet);
}
#region 分析配置
TemplateSettingSheet sheetSetting = new TemplateSettingSheet(workSheet);
#endregion
#region 填充数据
foreach (var item in sheetSetting.FreeSettingList)
{
if (string.IsNullOrEmpty(item.AddressLeftTop) || string.IsNullOrEmpty(item.AddressRightBottom))
{
continue;
}
ExcelRange range = workSheet.Cells[item.AddressLeftTop + "," + item.AddressRightBottom];
if (!dataSource.DataEntity.Keys.Contains(item.DataSource))
{
continue;
}
var data = dataSource.DataEntity[item.DataSource];
foreach (var field in sheetSetting.FieldSettingList)
{
if (ExcelCommon.IsCellInRange(item.FromRow, item.FromCol, item.ToRow, item.ToCol, field.CurrentCell))
{
var fieldName = field.Field;
var value = field.CurrentCell.Value?.ToString() ?? "";
value = value.Replace(field.SettingString, "");
object fieldData = null;
if (data.Keys.Contains(field.Field))
{
fieldData = data[field.Field];
}
field.CurrentCell.Value = value + fieldData;
}
}
}
List <string> mergedList = new List <string>();
foreach (var item in workSheet.MergedCells)
{
mergedList.Add(item.Replace(":", ","));
}
Dictionary <TemplateSettingRange, int> regionAddDic = new Dictionary <TemplateSettingRange, int>();
foreach (var item in sheetSetting.GridSettingList)
{
if (string.IsNullOrEmpty(item.AddressLeftTop) || string.IsNullOrEmpty(item.AddressRightBottom))
{
continue;
}
ExcelRange range = workSheet.Cells[item.AddressLeftTop + "," + item.AddressRightBottom];
if (!dataSource.DataList.Keys.Contains(item.DataSource))
{
continue;
}
var data = dataSource.DataList[item.DataSource];
//分析
int offsetCount = 0;
int addCount = 0;
int sameCount = 0;
int emptyCount = 0;
if (data.Count > 0 && data.First().Value.Count() > 1)
{
addCount = data.First().Value.Count() - 1;
}
foreach (var addItem in regionAddDic)
{
if (addItem.Key.FromRow < item.FromRow && addItem.Value > offsetCount)
{
offsetCount = addItem.Value;
}
else if (addItem.Key.FromRow == item.FromRow)
{
emptyCount = addItem.Value > addCount ? addItem.Value - addCount : 0;
addCount = addItem.Value > addCount?0:addCount - addItem.Value;
sameCount = addItem.Value > addCount ? 0 : addItem.Value;
}
}
//动态添加行
if (addCount > 0)
{
workSheet.InsertRow(item.FromRow + offsetCount + 1, addCount);
regionAddDic.Add(item, addCount + sameCount);
}
foreach (var field in sheetSetting.FieldSettingList)
{
if (ExcelCommon.IsCellInRange(item.FromRow, item.FromCol, item.ToRow, item.ToCol, field.CurrentCell))
{
var fieldName = field.Field;
List <object> fieldDatas = new List <object>();
if (data.Keys.Contains(field.Field))
{
bool isFieldMerge = false;
int fromRow = 1;
int fromCol = 1;
int toRow = 1;
int toCol = 1;
foreach (var merge in mergedList)
{
var arryMerge = merge.Split(new char[1] {
','
}, StringSplitOptions.RemoveEmptyEntries);
if (arryMerge.Length == 2)
{
var isMerge = ExcelCommon.IsCellInRange(arryMerge[0], arryMerge[1], field.CurrentCell);
if (isMerge)
{
isFieldMerge = true;
ExcelCommon.CalcRowCol(arryMerge[0], out fromRow, out fromCol);
ExcelCommon.CalcRowCol(arryMerge[1], out toRow, out toCol);
break;
}
}
}
fieldDatas = data[field.Field];
for (var i = 0; i < fieldDatas.Count; i++)
{
field.CurrentCell.Offset(i + offsetCount, 0).Value = fieldDatas[i];
if (i == 0)
{
continue;
}
if (isFieldMerge)
{
var oldRange = workSheet.Cells[fromRow + offsetCount, fromCol, toRow + offsetCount, toCol];
var newRange = workSheet.Cells[fromRow + offsetCount + i, fromCol, toRow + offsetCount + i, toCol];
newRange.Merge = true;
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
else
{
var oldRange = field.CurrentCell.Offset(offsetCount, 0);
var newRange = field.CurrentCell.Offset(i + offsetCount, 0);
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
}
if (emptyCount > 0)
{
for (var i = 0; i < emptyCount; i++)
{
if (isFieldMerge)
{
var oldRange = workSheet.Cells[fromRow + offsetCount, fromCol, toRow + offsetCount, toCol];
var newRange = workSheet.Cells[fromRow + offsetCount + fieldDatas.Count + i, fromCol, toRow + offsetCount + fieldDatas.Count + i, toCol];
newRange.Merge = true;
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
else
{
var oldRange = field.CurrentCell.Offset(offsetCount, 0);
var newRange = field.CurrentCell.Offset(i + offsetCount + fieldDatas.Count, 0);
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
}
}
}
}
}
}
#endregion
return(workSheet);
}
public TemplateSettingSheet(ExcelWorksheet sheet)
{
_FreeSettingList = new List <TemplateSettingRange>();
_GridSettingList = new List <TemplateSettingRange>();
_FieldSettingList = new List <TemplateSettingRange>();
if (sheet == null || sheet.Cells.Count() <= 0)
{
return;
}
foreach (var cell in sheet.Cells)
{
var cellValue = cell.Value?.ToString() ?? "";
var arry = cellValue.Split(new char[2] {
'{', '}'
}, StringSplitOptions.RemoveEmptyEntries);
if (arry.Length == 0)
{
continue;
}
foreach (var item in arry)
{
if (item.IndexOf(":") > -1 && item.IndexOf(";") > -1)
{
var settingItemArry = item.Split(new char[1] {
';'
}, StringSplitOptions.RemoveEmptyEntries);
if (settingItemArry.Length == 0)
{
continue;
}
var setting = new TemplateSettingRange();
foreach (var arryItem in settingItemArry)
{
var settingItem = arryItem.Split(':');
if (settingItem.Length < 2)
{
continue;
}
var key = settingItem[0];
var value = settingItem[1];
if (string.IsNullOrEmpty(key))
{
continue;
}
switch (key.ToUpper())
{
case "TYPE": { setting.Type = value.ToUpper(); } break;
case "DATASOURCE": { setting.DataSource = value.ToUpper(); } break;
case "ADDRESSLEFTTOP": { setting.AddressLeftTop = value.ToUpper(); } break;
case "ADDRESSRIGHTBOTTOM": { setting.AddressRightBottom = value.ToUpper(); } break;
case "FIELD": { setting.Field = value.ToUpper(); } break;
}
}
setting.CurrentCell = cell;
setting.SettingString = "{" + item + "}";
if (string.IsNullOrEmpty(setting.Type))
{
continue;
}
else if (setting.Type == "GRID")
{
ExcelCommon.CalcRowCol(setting.AddressLeftTop, out int fromRow, out int fromCol);
ExcelCommon.CalcRowCol(setting.AddressRightBottom, out int toRow, out int toCol);
setting.FromRow = fromRow;
setting.FromCol = fromCol;
setting.ToRow = toRow;
setting.ToCol = toCol;
_GridSettingList.Add(setting);
}
else if (setting.Type == "FREE")
{
ExcelCommon.CalcRowCol(setting.AddressLeftTop, out int fromRow, out int fromCol);
ExcelCommon.CalcRowCol(setting.AddressRightBottom, out int toRow, out int toCol);
setting.FromRow = fromRow;
setting.FromCol = fromCol;
setting.ToRow = toRow;
setting.ToCol = toCol;
_FreeSettingList.Add(setting);
}
else if (setting.Type == "VALUE")
{
_FieldSettingList.Add(setting);
}
}
var cellValueStr = cell.Value?.ToString() ?? "";
cell.Value = cellValueStr.Replace("{" + item + "}", "");
}
}
}
public TemplateSettingSheet(ExcelWorksheet sheet)
{
_FreeSettingList = new List <TemplateSettingRangeFree>();
_GridSettingList = new List <TemplateSettingRangeGrid>();
//_ChartSettingList = new List<TemplateSettingRangeChart>();
_FieldSettingList = new List <TemplateSettingField>();
if (sheet == null || sheet.Cells.Count() <= 0)
{
return;
}
foreach (var cell in sheet.Cells)
{
var cellValue = cell.Value?.ToString() ?? "";
var arry = cellValue.Split(new char[2] {
'{', '}'
}, StringSplitOptions.RemoveEmptyEntries);
if (arry.Length == 0)
{
continue;
}
foreach (var item in arry)
{
if (item.IndexOf(":") > -1 && item.IndexOf(";") > -1)
{
var settingItemArry = item.Split(new char[1] {
';'
}, StringSplitOptions.RemoveEmptyEntries);
if (settingItemArry.Length == 0)
{
continue;
}
foreach (var settingItem in settingItemArry)
{
if (settingItem.ToUpper().IndexOf("TYPE") < 0)
{
continue;
}
var arrItem = settingItem.Split(new char[1] {
':'
}, StringSplitOptions.RemoveEmptyEntries);
if (arrItem.Length < 2)
{
continue;
}
var key = arrItem[0];
var value = arrItem[1];
switch (value.ToUpper())
{
case "FREE": {
var freeItem = TemplateSettingRangeFree.Create(cell);
_FreeSettingList.Add(freeItem);
} break;
case "GRID":
{
var gridItem = TemplateSettingRangeGrid.Create(cell);
_GridSettingList.Add(gridItem);
}
break;
case "CHART":
{
//var chartItem = TemplateSettingRangeChart.Create(cell);
//_ChartSettingList.Add(chartItem);
}
break;
case "VALUE":
{
var fieldItem = TemplateSettingField.Create(cell);
_FieldSettingList.Add(fieldItem);
}
break;
}
}
}
var cellValueStr = cell.Value?.ToString() ?? "";
cell.Value = cellValueStr.Replace("{" + item + "}", "");
}
//自由格式
foreach (var free in _FreeSettingList)
{
foreach (var field in _FieldSettingList)
{
var isContain = ExcelCommon.IsCellInRange(free.AddressLeftTop, free.AddressRightBottom, field.CurrentCell);
if (isContain)
{
free.Fields.Add(field);
}
}
}
//表格
foreach (var grid in _GridSettingList)
{
foreach (var field in _FieldSettingList)
{
var isContain = ExcelCommon.IsCellInRange(grid.AddressLeftTop, grid.AddressRightBottom, field.CurrentCell);
if (isContain)
{
grid.Fields.Add(field);
}
}
}
//图表
}
}
protected override void AnalyseSetting()
{
var cellValue = cell.Value?.ToString() ?? "";
var arry = cellValue.Split(new char[2] {
'{', '}'
}, StringSplitOptions.RemoveEmptyEntries);
if (arry.Length == 0)
{
continue;
}
foreach (var item in arry)
{
if (item.IndexOf(":") > -1 && item.IndexOf(";") > -1)
{
var settingItemArry = item.Split(new char[1] {
';'
}, StringSplitOptions.RemoveEmptyEntries);
if (settingItemArry.Length == 0)
{
continue;
}
foreach (var settingItem in settingItemArry)
{
if (settingItem.ToUpper().IndexOf("TYPE") < 0)
{
continue;
}
var arrItem = settingItem.Split(new char[1] {
':'
}, StringSplitOptions.RemoveEmptyEntries);
if (arrItem.Length < 2)
{
continue;
}
var key = arrItem[0];
var value = arrItem[1];
switch (value.ToUpper())
{
case "FREE":
{
var freeItem = TemplateSettingRangeFree.Create(cell);
_FreeSettingList.Add(freeItem);
}
break;
case "GRID":
{
var gridItem = TemplateSettingRangeGrid.Create(cell);
_GridSettingList.Add(gridItem);
}
break;
case "CHART":
{
var chartItem = TemplateSettingRangeChart.Create(cell);
_ChartSettingList.Add(chartItem);
}
break;
case "VALUE":
{
var fieldItem = TemplateSettingField.Create(cell);
_FieldSettingList.Add(fieldItem);
}
break;
}
}
#region free配置分解
#endregion
TemplateSettingRange setting = null;
var type = "";
var chartSubType = "";
var title = "";
foreach (var arryItem in settingItemArry)
{
var settingItem = arryItem.Split(':');
if (settingItem.Length < 2)
{
continue;
}
var key = settingItem[0];
var value = settingItem[1];
if (string.IsNullOrEmpty(key))
{
continue;
}
switch (key.ToUpper())
{
case "TYPE": { type = value.ToUpper(); } break;
case "SUBTYPE": { chartSubType = value.ToUpper(); } break;
case "DATASOURCE": { setting.DataSource = value.ToUpper(); } break;
case "ADDRESSLEFTTOP": { setting.AddressLeftTop = value.ToUpper(); } break;
case "ADDRESSRIGHTBOTTOM": { setting.AddressRightBottom = value.ToUpper(); } break;
case "FIELD":
{
var fieldSetting = new TemplateSettingField();
fieldSetting.Field = value.ToUpper();
fieldSetting.Content = "{" + item + "}";
fieldSetting.CurrentCell = cell;
_FieldSettingList.Add(fieldSetting);
}
break;
//case "TITLE": { title = value; }break;
//case "WIDTH": {int.TryParse(value,out int width); setting.ChartWidth = width; } break;
//case "HEIGHT": { int.TryParse(value, out int height); setting.ChartHeight = height; } break;
//case "ISCUSTOMSIZE": { setting.IsCustomSize = (value == "1") ?true : false; }break;
}
}
if (string.IsNullOrEmpty(type))
{
continue;
}
else if (type == "GRID")
{
setting = new TemplateSettingRangeGrid();
setting.CurrentCell = cell;
setting.Content = "{" + item + "}";
ExcelCommon.CalcRowCol(setting.AddressLeftTop, out int fromRow, out int fromCol);
ExcelCommon.CalcRowCol(setting.AddressRightBottom, out int toRow, out int toCol);
setting.FromRow = fromRow;
setting.FromCol = fromCol;
setting.ToRow = toRow;
setting.ToCol = toCol;
_GridSettingList.Add(setting);
}
else if (type == "FREE")
{
setting = new TemplateSettingRangeFree();
setting.CurrentCell = cell;
setting.Content = "{" + item + "}";
ExcelCommon.CalcRowCol(setting.AddressLeftTop, out int fromRow, out int fromCol);
ExcelCommon.CalcRowCol(setting.AddressRightBottom, out int toRow, out int toCol);
setting.FromRow = fromRow;
setting.FromCol = fromCol;
setting.ToRow = toRow;
setting.ToCol = toCol;
_FreeSettingList.Add(setting);
}
else if (type == "CHART")
{
setting = new TemplateSettingRangeChart();
setting.CurrentCell = cell;
setting.Content = "{" + item + "}";
ExcelCommon.CalcRowCol(setting.AddressLeftTop, out int fromRow, out int fromCol);
ExcelCommon.CalcRowCol(setting.AddressRightBottom, out int toRow, out int toCol);
setting.FromRow = fromRow;
setting.FromCol = fromCol;
setting.ToRow = toRow;
setting.ToCol = toCol;
_ChartSettingList.Add(setting);
//if (string.IsNullOrEmpty(chartSubType))
//{
// setting.ChartType = EnumChartType.Chart;
//}
//else
//{
// switch (chartSubType)
// {
// case "CHART": { setting.ChartType = EnumChartType.Chart; } break;
// case "BARCHART": { setting.ChartType = EnumChartType.BarChart; } break;
// case "BUBBLECHART": { setting.ChartType = EnumChartType.BubbleChart; } break;
// case "DOUGHNUTCHART": { setting.ChartType = EnumChartType.DoughnutChart; } break;
// case "LINECHART": { setting.ChartType = EnumChartType.LineChart; } break;
// case "OFPIECHART": { setting.ChartType = EnumChartType.OfPieChart; } break;
// case "PIECHART": { setting.ChartType = EnumChartType.PieChart; } break;
// case "RADARCHART": { setting.ChartType = EnumChartType.RadarChart; } break;
// case "SCATTERCHART": { setting.ChartType = EnumChartType.ScatterChart; } break;
// case "SURFACECHART": { setting.ChartType = EnumChartType.SurfaceChart; } break;
// default: { setting.ChartType = EnumChartType.Chart; } break;
// }
//}
}
else if (type == "VALUE")
{
_FieldSettingList.Add(setting);
}
}
var cellValueStr = cell.Value?.ToString() ?? "";
cell.Value = cellValueStr.Replace("{" + item + "}", "");
}
}
/// <summary>
/// 填充报表(待重构)
/// </summary>
/// <param name="workSheet"></param>
/// <param name="dataSource"></param>
/// <returns></returns>
public ExcelWorksheet FillSheetData(ExcelWorksheet workSheet, ExcelObject dataSource)
{
if (workSheet == null || dataSource == null)
{
return(workSheet);
}
#region 分析配置
TemplateSettingSheet sheetSetting = new TemplateSettingSheet(workSheet);
#endregion
#region 填充数据
foreach (var item in sheetSetting.FreeSettingList)
{
item.Draw(workSheet, dataSource);
}
//重新计算表格位置
var cloneList = new List <TemplateSettingRangeGrid>();
using (var ms = new MemoryStream())
{
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(ms, sheetSetting.GridSettingList);
ms.Seek(0, 0);
cloneList = (List <TemplateSettingRangeGrid>)bf.Deserialize(ms);
}
foreach (var item in sheetSetting.GridSettingList)
{
var newRow = 0;
var newCol = 0;
var topList = new List <KeyValuePair <ExcelRange, int> >();
//查找导致其位移的其他表格
foreach (var otherGrid in cloneList.SkipWhile(o => o.AddressLeftTop == item.AddressLeftTop && o.AddressRightBottom == item.AddressRightBottom))
{
if (string.IsNullOrEmpty(otherGrid.AddressLeftTop) || string.IsNullOrEmpty(otherGrid.AddressRightBottom))
{
continue;
}
if (!dataSource.DataList.Keys.Contains(otherGrid.DataSource))
{
continue;
}
var data = dataSource.DataList[otherGrid.DataSource];
var count = data.FirstOrDefault().Value.Count();
if (count <= 1)
{
continue;
}
var offCount = count - 1;
var newRange = workSheet.Cells[otherGrid.FromRow + 3, otherGrid.FromCol, otherGrid.ToRow + 3, otherGrid.ToCol];
}
//计算真实位移
}
List <string> mergedList = new List <string>();
foreach (var item in workSheet.MergedCells)
{
mergedList.Add(item.Replace(":", ","));
}
Dictionary <TemplateSettingRange, int> regionAddDic = new Dictionary <TemplateSettingRange, int>();
foreach (var item in sheetSetting.GridSettingList)
{
if (string.IsNullOrEmpty(item.AddressLeftTop) || string.IsNullOrEmpty(item.AddressRightBottom))
{
continue;
}
ExcelRange range = workSheet.Cells[item.AddressLeftTop + "," + item.AddressRightBottom];
if (!dataSource.DataList.Keys.Contains(item.DataSource))
{
continue;
}
var data = dataSource.DataList[item.DataSource];
//分析
int offsetCount = 0;
int addCount = 0;
int sameCount = 0;
int emptyCount = 0;
if (data.Count > 0 && data.First().Value.Count() > 1)
{
addCount = data.First().Value.Count() - 1;
}
foreach (var addItem in regionAddDic)
{
if (addItem.Key.FromRow < item.FromRow && addItem.Value > offsetCount)
{
offsetCount = addItem.Value;
}
else if (addItem.Key.FromRow == item.FromRow)
{
emptyCount = addItem.Value > addCount ? addItem.Value - addCount : 0;
addCount = addItem.Value > addCount?0:addCount - addItem.Value;
sameCount = addItem.Value > addCount ? 0 : addItem.Value;
}
}
//动态添加行
if (addCount > 0)
{
workSheet.InsertRow(item.FromRow + offsetCount + 1, addCount);
regionAddDic.Add(item, addCount + sameCount);
}
foreach (var field in item.Fields)
{
var fieldName = field.Field;
List <object> fieldDatas = new List <object>();
if (data.Keys.Contains(field.Field))
{
bool isFieldMerge = false;
int fromRow = 1;
int fromCol = 1;
int toRow = 1;
int toCol = 1;
foreach (var merge in mergedList)
{
var arryMerge = merge.Split(new char[1] {
','
}, StringSplitOptions.RemoveEmptyEntries);
if (arryMerge.Length == 2)
{
var isMerge = ExcelCommon.IsCellInRange(arryMerge[0], arryMerge[1], field.CurrentCell);
if (isMerge)
{
isFieldMerge = true;
ExcelCommon.CalcRowCol(arryMerge[0], out fromRow, out fromCol);
ExcelCommon.CalcRowCol(arryMerge[1], out toRow, out toCol);
break;
}
}
}
fieldDatas = data[field.Field];
for (var i = 0; i < fieldDatas.Count; i++)
{
field.CurrentCell.Offset(i + offsetCount, 0).Value = fieldDatas[i];
if (i == 0)
{
continue;
}
if (isFieldMerge)
{
var oldRange = workSheet.Cells[fromRow + offsetCount, fromCol, toRow + offsetCount, toCol];
var newRange = workSheet.Cells[fromRow + offsetCount + i, fromCol, toRow + offsetCount + i, toCol];
newRange.Merge = true;
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
else
{
var oldRange = field.CurrentCell.Offset(offsetCount, 0);
var newRange = field.CurrentCell.Offset(i + offsetCount, 0);
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
}
if (emptyCount > 0)
{
for (var i = 0; i < emptyCount; i++)
{
if (isFieldMerge)
{
var oldRange = workSheet.Cells[fromRow + offsetCount, fromCol, toRow + offsetCount, toCol];
var newRange = workSheet.Cells[fromRow + offsetCount + fieldDatas.Count + i, fromCol, toRow + offsetCount + fieldDatas.Count + i, toCol];
newRange.Merge = true;
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
else
{
var oldRange = field.CurrentCell.Offset(offsetCount, 0);
var newRange = field.CurrentCell.Offset(i + offsetCount + fieldDatas.Count, 0);
ExcelCommon.HandleCellStyle(oldRange, newRange);
}
}
}
}
}
}
#endregion
#region 图表
#endregion
return(workSheet);
}
