public static object GetCellValue(HSSFFormulaEvaluator evaluator, HSSFCell cell)
{
object value = null;
switch (cell.CellType)
{
case CellType.BLANK:
case CellType.Unknown:
value = Convert.ToString(cell.StringCellValue);
break;
case CellType.BOOLEAN:
value = cell.BooleanCellValue;
break;
case CellType.ERROR:
value = Convert.ToString(cell.ErrorCellValue);
break;
case CellType.FORMULA:
try
{
cell = evaluator.EvaluateInCell(cell) as HSSFCell;
value = GetCellValue(evaluator, cell);
}
catch (Exception)
{
string err = String.Format("单元格{0}使用了不支持的表达式：{1}，请移除该表达式", YZExcelHelper.ColumnIndexToName(cell.ColumnIndex) + (cell.RowIndex + 1).ToString(), cell.CellFormula);
throw new Exception(err);
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue;
}
else
{
value = cell.NumericCellValue;
}
break;
case CellType.STRING:
value = Convert.ToString(cell.RichStringCellValue);
break;
default:
value = Convert.ToString(cell.StringCellValue);
break;
}
if (value is string && value != null)
{
value = (value as string).Trim();
}
return(value);
}
public static DataSet LoadExcel(Stream stream, string fileName, int titleRowIndex, int dataRowIndex, bool ignoreCellException)
{
//打开文件
IWorkbook book = null;
IFormulaEvaluator evaluator = null;
string ext = Path.GetExtension(fileName);
if (YZStringHelper.EquName(ext, ".xlsx"))
{
book = new XSSFWorkbook(stream);
evaluator = new XSSFFormulaEvaluator(book);
}
if (book == null)
{
book = new HSSFWorkbook(stream);
evaluator = new HSSFFormulaEvaluator(book);
}
DataSet dataset = new DataSet();
for (int i = 0; i < book.NumberOfSheets; i++)
{
ISheet sheet = (ISheet)book.GetSheetAt(i);
DataTable table = new DataTable(sheet.SheetName);
dataset.Tables.Add(table);
if (sheet.LastRowNum != 0)
{
for (int r = 0; r <= sheet.LastRowNum; r++)
{
IRow row = (IRow)sheet.GetRow(r);
if (row == null) //存在row为null的情况
{
break; // continue;
}
DataRow dataRow = null;
bool containsValue = false;
for (int c = 0; c < row.LastCellNum; c++)
{
ICell cell = (ICell)row.GetCell(c); //存在cell为null的情况
object value = cell == null ? null : YZExcelHelper.GetCellValue(evaluator, cell, ignoreCellException);
if (!String.IsNullOrEmpty(Convert.ToString(value)))
{
containsValue = true;
}
string columnName = YZExcelHelper.ColumnIndexToName(c);
if (r == titleRowIndex)
{
DataColumn dataColumn = new DataColumn(columnName, typeof(object));
table.Columns.Add(dataColumn);
dataColumn.Caption = Convert.ToString(value);
}
else if (r >= dataRowIndex)
{
if (!table.Columns.Contains(columnName))
{
DataColumn dataColumn = new DataColumn(columnName, typeof(object));
table.Columns.Add(dataColumn);
}
if (dataRow == null)
{
dataRow = table.NewRow();
}
dataRow[columnName] = value;
if (c == row.LastCellNum - 1 && containsValue)
{
table.Rows.Add(dataRow);
}
}
}
}
}
}
return(dataset);
}
public static object GetCellValue(IFormulaEvaluator evaluator, ICell cell, bool ignoreCellException)
{
object value = null;
switch (cell.CellType)
{
case CellType.Blank:
case CellType.Unknown:
value = Convert.ToString(cell.StringCellValue);
break;
case CellType.Boolean:
value = cell.BooleanCellValue;
break;
case CellType.Error:
value = Convert.ToString(cell.ErrorCellValue);
break;
case CellType.Formula:
try
{
cell = evaluator.EvaluateInCell(cell) as ICell;
value = GetCellValue(evaluator, cell, ignoreCellException);
}
catch (Exception)
{
if (ignoreCellException)
{
value = "";
}
else
{
string formula = null;
try
{
formula = cell.CellFormula;
}
catch (Exception)
{
}
string err = String.Format(Resources.YZStrings.Aspx_Excel_Ext_Invalid_Express, YZExcelHelper.ColumnIndexToName(cell.ColumnIndex) + (cell.RowIndex + 1).ToString(), formula);
throw new Exception(err);
}
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue;
}
else
{
value = cell.NumericCellValue;
}
break;
case CellType.String:
value = Convert.ToString(cell.RichStringCellValue);
break;
default:
value = Convert.ToString(cell.StringCellValue);
break;
}
if (value is string && value != null)
{
value = (value as string).Trim();
}
return(value);
}
public static HSSFWorkbook NoTemplateExport(ColumnDefineCollection columnDefines, DataTable table)
{
//创建工作簿
HSSFWorkbook book = new HSSFWorkbook();
//增加标题Style
HSSFCellStyle styleHeader = (HSSFCellStyle)book.CreateCellStyle();
styleHeader.FillForegroundColor = HSSFColor.BLUE.index;
styleHeader.FillPattern = FillPatternType.SOLID_FOREGROUND;
//设置Font
HSSFFont fontHeader = (HSSFFont)book.CreateFont();
fontHeader.FontName = "Tahoma";
fontHeader.FontHeight = 200;
fontHeader.Color = HSSFColor.WHITE.index;
styleHeader.SetFont(fontHeader);
//设置缺省Style
HSSFCellStyle styleDefault = (HSSFCellStyle)book.CreateCellStyle();
HSSFFont fontDefault = (HSSFFont)book.CreateFont();
styleDefault.SetFont(fontDefault);
fontDefault.FontName = "Tahoma";
fontDefault.FontHeight = 200;
//创建Sheet
HSSFRow row;
HSSFCell cell;
HSSFSheet sheet = (HSSFSheet)book.CreateSheet("Sheet1");
//创建标题列
row = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < columnDefines.Count; i++)
{
ColumnDefine columnDefine = columnDefines[i];
//创建cell
cell = (HSSFCell)row.CreateCell(i, CellType.STRING);
//应用style
HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle();
style.CloneStyleFrom(styleHeader);
style.Alignment = columnDefine.Align;
cell.CellStyle = style;
//设置值
cell.SetCellValue(columnDefine.Text);
sheet.SetColumnWidth(i, YZExcelHelper.PixelToExcel(columnDefine.Width));
}
foreach (DataRow dataRow in table.Rows)
{
row = (HSSFRow)sheet.CreateRow(sheet.LastRowNum + 1);
for (int i = 0; i < columnDefines.Count; i++)
{
ColumnDefine columnDefine = columnDefines[i];
object objValue = dataRow[columnDefine.ColumnName];
//SQL Server数据库中monery4位小数点处理
if (objValue is decimal)
{
objValue = (decimal)Decimal.ToDouble((decimal)objValue);
}
//创建cell
cell = (HSSFCell)row.CreateCell(i, CellType.STRING);
//应用style
if (columnDefine.Style == null)
{
columnDefine.Style = (HSSFCellStyle)book.CreateCellStyle();
columnDefine.Style.CloneStyleFrom(styleDefault);
columnDefine.Style.Alignment = columnDefine.Align;
}
cell.CellStyle = columnDefine.Style;
//设置值
TypeCode typeCode = Type.GetTypeCode(objValue == null ? typeof(string) : objValue.GetType());
switch (typeCode)
{
case TypeCode.Boolean:
cell.SetCellValue(Convert.ToBoolean(objValue));
break;
case TypeCode.DateTime:
DateTime date = (DateTime)objValue;
string strValue = date == DateTime.MinValue ? "" : YZStringHelper.DateToStringL(date);
cell.SetCellValue(strValue);
break;
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Single:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
case TypeCode.SByte:
case TypeCode.Byte:
cell.SetCellValue(Convert.ToDouble(objValue));
break;
default:
cell.SetCellValue(Convert.ToString(objValue));
break;
}
}
}
return(book);
}
//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);
}
}
