好不容易做出来了导出Excel文件,却发现模板假如有合并单元格问题,就复制不上去,而且还有赋值问题等也困扰了好几天。今天终于解决了。(*^__^*) 嘻嘻
补充一下:还有读取模板已使用区域的行数与列数。直接规定的反应好慢好慢。
int startedrow = workSheet.UsedRange.Row;//模板已使用区域的起始行
int startedcol = workSheet.UsedRange.Column;//模板已使用区域的起始列
if( rowCount == 0 ) rowCount = workSheet.UsedRange.Rows.Count + startedrow - 1;//模板已使用区域的行数
if( columnCount == 0 ) columnCount = workSheet.UsedRange.Columns.Count + startedcol - 1;//模板已使用区域的列数
这样就可以直接调用GetSubstCellList( phyFileTar, 1, 0, 0 );了。
static private void CopyExcelFormat(Excel.Worksheet workSheet, ArrayList dataList, ArrayList subsItemList)
{
foreach( SubstCell substCell in subsItemList )
{
string cellStr = substCell.cellStr; //定义的单元格内容
ArrayList strList = UtlMisc.GetItemsListFromString( cellStr, ":" ); //单元格内容用":"隔开
int dataIndex = int.Parse( strList[0].ToString() ); //取数据集中第几张表
string fieldName = strList[1].ToString(); //取表中的字段名
bool isList = strList[2].ToString().ToUpper() == "T"; //判断是否是多行记录
if( isList ){
int r = substCell.rowIndex;
int c = substCell.coluIndex;
Excel.Range sRange = workSheet.get_Range(workSheet.Cells[r, c], workSheet.Cells[r, c]);
DataTable tbl = dataList[dataIndex] as DataTable;
int rCount = tbl.Rows.Count;
for( int inc = 1; inc < rCount; ++inc ){
bool isMerge = (bool)sRange.MergeCells;//判断是否为合并单元格
if( isMerge ){
int mergerow = sRange.MergeArea.Rows.Count;//得到合并单元格的横格数
int mergecol = sRange.MergeArea.Columns.Count;//得到合并单元格的列格数
Excel.Range tRange = workSheet.get_Range( workSheet.Cells[r + inc * mergerow, c], workSheet.Cells[r + (inc + 1) * mergerow - 1, c + mergecol - 1] );//合并单元格的区域
sRange.Copy( tRange );
}else{
Excel.Range tRange = workSheet.get_Range( workSheet.Cells[r + inc, c], workSheet.Cells[r + inc, c] );
sRange.Copy( tRange );
if ( copyRowCount < rCount ){//不加这一句最后一行会显示一个格式0:MAmount:F
for ( int j = 1; j < workSheet.UsedRange.Columns.Count - 1 + c; j++ ) workSheet.Cells[inc + r, j] = "";
}
}
}
}
}
}
static private void FillSubstCellData( Excel.Worksheet workSheet, ArrayList dataList, SubstCell substCellItem )
{
string cellStr = substCellItem.cellStr;//定义的单元格内容
ArrayList strList = UtlMisc.GetItemsListFromString( cellStr, ":" ); //单元格内容用":"隔开
int dataIndex = int.Parse(strList[0].ToString()); //取数据集中第几张表
string fieldName = strList[1].ToString(); //取表中的字段名
bool isList = strList[2].ToString().ToUpper() == "T"; //判断是否是多行记录
if(isList)
{ //如果是多行记录
DataTable tbl = dataList[dataIndex] as DataTable; //把符合条件的转换成datatable类型
int r = substCell.rowIndex;
int c = substCell.coluIndex;
Excel.Range sRange = workSheet.get_Range(workSheet.Cells[r, c], workSheet.Cells[r, c]);
int rCount = tbl.Rows.Count;
for(int i = 0; i < tbl.Rows.Count; i++)
{
bool isMerge = (bool)sRange.MergeCells;//判断是否为合并单元格
if( isMerge ){
DataTable tb2 = dataList[dataIndex] as DataTable;
int mergerow = sRange.MergeArea.Rows.Count;//合并单元格区域的横格数
int mergecol = sRange.MergeArea.Columns.Count;//合并单元格的列格数
workSheet.Cells[r + i * mergerow, c] = tb2.Rows[i][fieldName];//为合并单元格赋值
}
else workSheet.Cells[r + i, c] = tbl.Rows[i][fieldName];
}
}
else workSheet.Cells[substCellItem.rowIndex, substCellItem.coluIndex] = ((DataTable)dataList[dataIndex]).Rows[0][fieldName];
}