//Grid填充
protected static void DoGridFill(HSSFWorkbook book, DataSet dataset)
{
HSSFSheet sheetFillDefine = (HSSFSheet)book.GetSheet("GridFill");
if (sheetFillDefine == null)
{
return;
}
GridDefineCollection gridDefines = new GridDefineCollection();
GridDefine gridDefine = null;
object curSection = null;
for (int r = 1; r <= sheetFillDefine.LastRowNum; r++)
{
HSSFRow excelRow = (HSSFRow)sheetFillDefine.GetRow(r);
if (excelRow == null)
{
continue;
}
//获得FixFill中的一设定行
string[] values = new string[12];
for (int i = 0; i <= values.Length - 1 && i <= excelRow.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)excelRow.GetCell(i);
if (cell == null)
{
continue;
}
string value;
if (cell.CellType == CellType.NUMERIC)
{
value = cell.NumericCellValue.ToString();
}
else
{
value = cell.StringCellValue;
}
if (value != null)
{
value = value.Trim();
}
values[i] = value;
}
string sheetName = values[0]; //填充到的sheet
string startRow = values[1]; //填充到的Cell
string blockRows = values[2]; //替换Cell中的一部分
string tableName = values[3]; //用那个表中的数据填充
string sectionName = values[4]; //用那列数据填充
string minRows = values[11]; //至少几行
//应用缺省值
if (String.IsNullOrEmpty(sheetName))
{
sheetName = book.GetSheetName(0);
}
if (!String.IsNullOrEmpty(startRow))
{
gridDefine = new GridDefine();
gridDefines.Add(gridDefine);
gridDefine.SheetName = sheetName;
gridDefine.Sheet = (HSSFSheet)book.GetSheet(sheetName);
gridDefine.StartRow = Int32.Parse(startRow) - 1;
gridDefine.BlockRowCount = Int32.Parse(blockRows);
gridDefine.FillTableName = tableName;
gridDefine.MinRows = 0;
Int32.TryParse(minRows, out gridDefine.MinRows);
}
if (gridDefine == null)
{
continue;
}
if (!String.IsNullOrEmpty(sectionName))
{
if (String.Compare(sectionName, "Fill", true) == 0)
{
curSection = gridDefine.GridCellFills;
}
else if (String.Compare(sectionName, "Template", true) == 0)
{
curSection = gridDefine.GridBlockTemplates;
}
else if (String.Compare(sectionName, "Group", true) == 0)
{
curSection = gridDefine.Groups;
}
else
{
curSection = null;
}
}
else
{
if (curSection is GridCellFillCollection)
{
GridCellFillCollection fills = curSection as GridCellFillCollection;
GridCellFill fill = new GridCellFill();
fills.Add(fill);
string cellName = values[5];
string cellVarName = values[6];
string fillColumnName = values[7];
string renderFunction = values[8];
string emptyText = values[9];
//应用缺省值
if (String.IsNullOrEmpty(renderFunction))
{
renderFunction = "DefaultRender";
}
Point cellPos = YZExcelHelper.CellNameToIndex(cellName);
fill.RowOffset = cellPos.Y - gridDefine.StartRow;
fill.ColumnIndex = cellPos.X;
fill.CellVarName = cellVarName;
fill.FillColumnName = fillColumnName;
fill.RenderFunction = renderFunction;
fill.EmptyText = emptyText;
}
else if (curSection is GridBlockTemplateCollection)
{
string templateName = values[5];
string startRowIndexStr = values[6];
string condition = values[7];
int startRowIndex = 0;
if (Int32.TryParse(startRowIndexStr, out startRowIndex))
{
GridBlockTemplateCollection templates = curSection as GridBlockTemplateCollection;
GridBlockTemplate template = new GridBlockTemplate(gridDefine);
templates.Add(template);
template.Name = templateName;
template.StartRow = startRowIndex - 1;
template.Condition = condition;
}
}
else if (curSection is GroupCollection)
{
string columnName = values[5];
if (!String.IsNullOrEmpty(columnName))
{
GroupCollection groups = curSection as GroupCollection;
Group group = new Group();
groups.Add(group);
group.ColumnName = columnName;
}
}
else
{
}
}
}
gridDefines.Sort();
foreach (GridDefine grid in gridDefines)
{
HSSFSheet sheetfill = grid.Sheet;
foreach (GridBlockTemplate template in grid.GridBlockTemplates)
{
for (int i = 0; i < grid.BlockRowCount; i++)
{
HSSFRow row = (HSSFRow)sheetfill.GetRow(template.StartRow + i);
template.Rows.Add(row);
}
for (int i = 0; i < sheetfill.NumMergedRegions; i++)
{
CellRangeAddress region = sheetfill.GetMergedRegion(i);
if (region.FirstRow >= template.StartRow && region.LastRow <= template.StartRow + grid.BlockRowCount - 1)
{
region = region.Copy();
region.FirstRow -= template.StartRow;
region.LastRow -= template.StartRow;
template.MergedRegions.Add(region);
}
}
for (int i = 0; i < book.NumberOfSheets; i++)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
continue;
}
foreach (HSSFChart chart in HSSFChart.GetSheetCharts(sheet))
{
foreach (HSSFChart.HSSFSeries series in chart.Series)
{
if (template.Contains(sheet, series))
{
SeriesTemplate seriesTemplate = new SeriesTemplate();
template.SeriesTemplates.Add(seriesTemplate);
seriesTemplate.Chart = chart;
seriesTemplate.Series = series;
}
}
}
}
}
if (grid.GridBlockTemplates.Count != 0)
{
grid.GridBlockTemplates[grid.GridBlockTemplates.Count - 1].Condition = null;
}
}
for (int i = 0; i < book.NumberOfSheets; i++)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
continue;
}
foreach (CellValueRecordInterface recInferface in sheet.Sheet.RowsAggregate.GetValueRecords())
{
if (recInferface is FormulaRecordAggregate)
{
FormulaRecordAggregate fraInterface = recInferface as FormulaRecordAggregate;
FormulaRecord formulaRecord = fraInterface.FormulaRecord;
if (formulaRecord.IsSharedFormula)
{
fraInterface.UnlinkSharedFormula();
}
}
}
}
foreach (GridDefine grid in gridDefines)
{
//创建空Grid
HSSFSheet sheetfill = grid.Sheet;
DataTable dataTable = dataset.Tables[grid.FillTableName];
int gridTagBlockCount = Math.Max(dataTable.Rows.Count, grid.MinRows);
int gridTagRowCount = grid.BlockRowCount * gridTagBlockCount;
//GroupData(dataTable);
//DataView view1 = new DataView(dataTable);
//view1.Sort = "City asc,Shop asc";
//dataTable = view1.ToTable();
if (dataTable != null && dataTable.Rows.Count != 0)
{
if (dataTable.Rows.Count != 0 && sheetfill.LastRowNum >= grid.TemplateEndRow + 1)
{
sheetfill.ShiftRows(grid.TemplateEndRow + 1, sheetfill.LastRowNum, gridTagRowCount, true, false);
}
for (int i = 0; i < sheetfill.NumMergedRegions; i++)
{
CellRangeAddress region = sheetfill.GetMergedRegion(i);
if (region.FirstRow <= grid.StartRow && region.LastRow >= grid.TemplateEndRow)
{
region.LastRow += Math.Max(dataTable.Rows.Count, grid.MinRows);
}
}
HSSFSheet hssfsheet = sheetfill as HSSFSheet;
foreach (CellValueRecordInterface recInferface in hssfsheet.Sheet.RowsAggregate.GetValueRecords())
{
if (recInferface is FormulaRecordAggregate)
{
FormulaRecord formulaRecord = ((FormulaRecordAggregate)recInferface).FormulaRecord;
Ptg[] ptgs = formulaRecord.ParsedExpression;
foreach (Ptg ptg in ptgs)
{
List <int> rowIndexs = new List <int>();
List <int> newRowIndexs = new List <int>();
if (ptg is RefPtgBase)
{
RefPtgBase refPtg = ptg as RefPtgBase;
rowIndexs.Add(refPtg.Row);
}
else if (ptg is AreaPtgBase)
{
AreaPtgBase aptg = ptg as AreaPtgBase;
rowIndexs.Add(aptg.FirstRow);
rowIndexs.Add(aptg.LastRow);
}
foreach (int rowIndex in rowIndexs)
{
int newRowIndex = rowIndex;
if (formulaRecord.Row < grid.StartRow || formulaRecord.Row > grid.TemplateEndRow)
{
if (rowIndex == grid.StartRow)
{
newRowIndex = grid.TemplateEndRow + 1;
}
if (rowIndex == grid.TemplateEndRow)
{
newRowIndex = grid.TemplateEndRow + gridTagRowCount;
}
}
newRowIndexs.Add(newRowIndex);
}
for (int i = 0; i < rowIndexs.Count; i++)
{
int rowIndex = rowIndexs[i];
int newRowIndex = newRowIndexs[i];
if (newRowIndex != rowIndex)
{
if (ptg is RefPtgBase)
{
RefPtgBase refPtg = ptg as RefPtgBase;
refPtg.Row = newRowIndex;
}
else if (ptg is AreaPtgBase)
{
AreaPtgBase aptg = ptg as AreaPtgBase;
if (i == 0)
{
aptg.FirstRow = newRowIndex;
}
else
{
aptg.LastRow = newRowIndex;
}
}
formulaRecord.ParsedExpression = ptgs;
}
}
}
}
}
for (int i = 0; i < book.NumberOfSheets; i++)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
continue;
}
foreach (RecordBase recbase in sheet.Sheet.Records)
{
if (recbase is LinkedDataRecord)
{
LinkedDataRecord link = recbase as LinkedDataRecord;
Ptg[] ptgs = ptgs = link.FormulaOfLink;
foreach (Ptg ptg in ptgs)
{
HSSFSheet sheetptg = PtgCollection.GetPtgSheet(sheet, ptg);
if (sheetptg == sheetfill)
{
if (ptg is RefPtgBase)
{
RefPtgBase refPtg = ptg as RefPtgBase;
if (refPtg.Row > grid.TemplateEndRow)
{
refPtg.Row += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
link.FormulaOfLink = ptgs;
}
}
if (ptg is AreaPtgBase)
{
AreaPtgBase areaPtg = ptg as AreaPtgBase;
if (areaPtg.FirstRow <= grid.StartRow && areaPtg.LastRow >= grid.TemplateEndRow)
{
areaPtg.LastRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
link.FormulaOfLink = ptgs;
}
else if (areaPtg.FirstRow > grid.TemplateEndRow)
{
areaPtg.FirstRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
areaPtg.LastRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
link.FormulaOfLink = ptgs;
}
}
}
}
}
}
}
dataTable.Columns.Add("_gridTemplateIndex", typeof(object));
foreach (GridBlockTemplate template in grid.GridBlockTemplates)
{
if (String.IsNullOrEmpty(template.Condition))
{
foreach (DataRow dataRow in dataTable.Rows)
{
if (Convert.IsDBNull(dataRow["_gridTemplateIndex"]))
{
dataRow["_gridTemplateIndex"] = template;
}
}
break;
}
DataView view = new DataView(dataTable);
view.RowFilter = template.Condition.Replace("$totalRow", dataTable.Rows.Count.ToString());
DataTable rvTable = view.ToTable();
foreach (DataRow dataRow in rvTable.Rows)
{
int rowIndex = Convert.ToInt32(dataRow["RowNum"]);
DataRow dataRowSrc = dataTable.Rows[rowIndex - 1];
dataRowSrc["_gridTemplateIndex"] = template;
}
}
for (int i = 0; i < gridTagBlockCount; i++)
{
GridBlockTemplate template;
if (i >= dataTable.Rows.Count)
{
template = grid.GridBlockTemplates[grid.GridBlockTemplates.Count - 1];
}
else
{
template = dataTable.Rows[i]["_gridTemplateIndex"] as GridBlockTemplate;
}
int startRowIndex = grid.TemplateEndRow + 1 + i * grid.BlockRowCount;
for (int j = 0; j < grid.BlockRowCount; j++)
{
int newRowIndex = startRowIndex + j;
HSSFRow newRow = (HSSFRow)sheetfill.GetRow(newRowIndex);
if (newRow == null)
{
newRow = (HSSFRow)sheetfill.CreateRow(newRowIndex);
}
if (template != null)
{
HSSFRow srcRow = template.Rows[j];
CopyRow(grid.BlockRowCount, j, srcRow, newRow);
}
}
foreach (CellRangeAddress region in template.MergedRegions)
{
sheetfill.AddMergedRegion(new CellRangeAddress(startRowIndex + region.FirstRow, startRowIndex + region.FirstRow, region.FirstColumn, region.LastColumn));
}
foreach (SeriesTemplate seriesTemplate in template.SeriesTemplates)
{
seriesTemplate.CloneSeries(sheetfill, i * grid.BlockRowCount - (template.StartRow - grid.StartRow));
}
}
foreach (GridBlockTemplate template in grid.GridBlockTemplates)
{
foreach (SeriesTemplate seriesTemplate in template.SeriesTemplates)
{
seriesTemplate.Chart.RemoveSeries(seriesTemplate.Series);
}
}
}
//删除模板
int addedExcelRowCount = 0;
if (dataTable != null)
{
addedExcelRowCount = dataTable.Rows.Count * grid.BlockRowCount;
}
for (int i = grid.StartRow; i <= grid.TemplateEndRow; i++)
{
sheetfill.CreateRow(i);
}
int firstRow = grid.StartRow;
int lastRow = grid.TemplateEndRow;
ShiftRows(sheetfill, grid.TemplateEndRow + 1, -(grid.TemplateEndRow - grid.StartRow + 1));
//填充数据
if (dataTable != null)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
foreach (GridCellFill fill in grid.GridCellFills)
{
Point cellPos = new Point();
cellPos.X = fill.ColumnIndex;
cellPos.Y = grid.StartRow + fill.RowOffset + i * grid.BlockRowCount;
//获得要填充的cell的行列序号
HSSFRow rowfill = (HSSFRow)sheetfill.GetRow(cellPos.Y);
if (rowfill == null)
{
continue;
}
//获得要填充的cell
HSSFCell cellfill = (HSSFCell)rowfill.GetCell(cellPos.X);
if (cellfill == null)
{
cellfill = (HSSFCell)rowfill.CreateCell(cellPos.X);
}
//获得填充值
object valuefill = null;
if (dataTable.Columns.Contains(fill.FillColumnName))
{
valuefill = row[fill.FillColumnName];
}
//执行填充
DoRender(fill.RenderFunction, cellfill, fill.CellVarName, dataset, valuefill, fill.EmptyText);
}
}
for (int i = dataTable.Rows.Count; i < grid.MinRows; i++)
{
foreach (GridCellFill fill in grid.GridCellFills)
{
Point cellPos = new Point();
cellPos.X = fill.ColumnIndex;
cellPos.Y = grid.StartRow + fill.RowOffset + i * grid.BlockRowCount;
//获得要填充的cell的行列序号
HSSFRow rowfill = (HSSFRow)sheetfill.GetRow(cellPos.Y);
if (rowfill == null)
{
continue;
}
//获得要填充的cell
HSSFCell cellfill = (HSSFCell)rowfill.GetCell(cellPos.X);
if (cellfill == null)
{
cellfill = (HSSFCell)rowfill.CreateCell(cellPos.X);
}
//获得填充值
cellfill.SetCellValue("");
}
}
MergeCells(sheetfill, grid, grid.Groups, 0, grid.StartRow, gridTagRowCount);
}
}
}
//固定填充
protected static void DoFixFill(HSSFWorkbook book, DataSet dataset)
{
HSSFSheet sheetFillDefine = (HSSFSheet)book.GetSheet("FixFill");
if (sheetFillDefine == null)
{
return;
}
for (int r = 1; r <= sheetFillDefine.LastRowNum; r++)
{
HSSFRow excelRow = (HSSFRow)sheetFillDefine.GetRow(r);
//获得FixFill中的一设定行
string[] values = new string[8];
for (int i = 0; i <= 7 && i <= excelRow.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)excelRow.GetCell(i);
if (cell == null)
{
continue;
}
string value = cell.StringCellValue;
if (value != null)
{
value = value.Trim();
}
values[i] = value;
}
string sheetName = values[0]; //填充到的sheet
string cellName = values[1]; //填充到的Cell
string varName = values[2]; //替换Cell中的一部分
string tableName = values[3]; //用那个表中的数据填充
string columnName = values[4]; //用那列数据填充
string filter = values[5]; //过滤条件.net DataView filter语法
string render = values[6]; //数据格式化函数 - 暂时不用
string emptyText = values[7]; //数据空时的显示文字
//应用缺省值
if (String.IsNullOrEmpty(render))
{
render = "DefaultRender";
}
if (String.IsNullOrEmpty(sheetName))
{
sheetName = book.GetSheetName(0);
}
//跳过空行
if (String.IsNullOrEmpty(cellName) ||
String.IsNullOrEmpty(tableName) ||
String.IsNullOrEmpty(columnName))
{
continue;
}
//获得填充用数据源表
DataTable srcTable = dataset.Tables[tableName];
if (srcTable == null)
{
continue;
}
//填充数据
DataTable table = srcTable;
//如设置了过滤条件,则过滤出所需数据
if (!String.IsNullOrEmpty(filter))
{
DataView view = new DataView(srcTable);
view.RowFilter = filter;
table = view.ToTable();
}
//获得填充到的sheet
HSSFSheet sheetfill = (HSSFSheet)book.GetSheet(sheetName);
if (sheetfill == null)
{
continue;
}
//获得要填充的cell的行列序号
Point cellPos = YZExcelHelper.CellNameToIndex(cellName);
HSSFRow rowfill = (HSSFRow)sheetfill.GetRow(cellPos.Y);
if (rowfill == null)
{
continue;
}
//获得要填充的cell
HSSFCell cellfill = (HSSFCell)rowfill.GetCell(cellPos.X);
if (cellfill == null)
{
continue;
}
//获得填充值
object valuefill = null;
if (table.Rows.Count != 0 && table.Columns.Contains(columnName))
{
valuefill = table.Rows[0][columnName];
}
//执行填充
DoRender(render, cellfill, varName, dataset, valuefill, emptyText);
}
}