导出Excel文件
private void Invoke()
{
ArrayList arrlist = new ArrayList();
DataTable tab1 = UtlBiz.GetDefaultTransaction().GetTable( "select * from Customers" );
DataTable tab2 = UtlBiz.GetDefaultTransaction().GetTable( "select * from Employees" );
arrlist.Add( tab1 );
arrlist.Add( tab2 );
string webFileTmp = "Files\aaa.xls";//模板路径
string webFileTar = "Files\ttt.xls";//目标路径
string phyFileTmp = Server.MapPath( webFileTmp );//模板路径
string phyFileTar = Server.MapPath( webFileTar );//目标路径
ExcDataOnTmpA( arrlist, phyFileTmp, phyFileTar );
}
//============================================================
//导出文件(需要源文件名和模板文件名)
//============================================================
private void ExcDataOnTmpA( ArrayList arrlist, string tmpFileName, string phyFileTar )
{
// Copy File to tarFileName
CopyFile( tmpFileName, phyFileTar );
ArrayList substItemList = GetSubstCellList( phyFileTar, 1, 100, 100 );
SubstituteCellData( arrlist, substItemList, phyFileTar );
}
//============================================================
//复制模板文件
//============================================================
private bool CopyFile( string phyFileTmp, string phyFileTar )
{
if( !File.Exists( phyFileTmp ))
return false;
try{
File.Copy( phyFileTmp, phyFileTar, true );
return true;
}catch( Exception ex ){
return false;
}
}
//============================================================
//填充单元格并保存目标文件
//============================================================
private void SubstituteCellData( ArrayList arrList, ArrayList subsItemList, string phyFileTar )
{
Excel.Application excelApp = new Excel.ApplicationClass(); //创建一个Application对象
Excel.Workbook workBook = excelApp.Workbooks._Open( phyFileTar, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value );
Excel.Worksheet workSheet = ( Excel.Worksheet )workBook.ActiveSheet;
try{
//遍历单元格
CopyExcelFormat( workSheet, arrList, subsItemList );
foreach ( SubstCell substCell in subsItemList ) FillSubstCellData( workSheet, arrList, substCell );//填充单元格
workBook.Save();
}catch( Exception Er ){
throw new Exception( "调用EXCEL程序出现错误！" + Er.Message );
}finally{
if( workBook != null ) workBook.Close( false, Missing.Value, Missing.Value );
if( excelApp != null ) excelApp.Quit();
}
}
//===============================================================
//验证格式是否为0:MAmount:F并判断是否是多行记录并填充数据。T代表的是多行
//===============================================================
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类型
//复制单元格格式
for ( int i = 0; i < tbl.Rows.Count - 1; i++ ){
workSheet.Cells[ substCellItem.rowIndex + i, substCellItem.coluIndex ] = tbl.Rows[i][fieldName]; }
}else{
workSheet.Cells[ substCellItem.rowIndex, substCellItem.coluIndex ] = ((DataTable)dataList[dataIndex]).Rows[0][fieldName];
}
}
//=============================================================
//复制格式
//=============================================================
private void CopyExcelFormat( Excel.Worksheet workSheet, ArrayList dataList, ArrayList subsItemList )
{
&n
补充：软件开发 , Java ,
