看我发帖时间就知道我找资料找得多辛苦了,实在是找不到,特来向广大高手求救!
做好了一个excel模板,里面有一个sheet,我现在想把DataTable里面的数据导出到一个复制的excel里面,DataTable里面的一条纪录对应一个sheet,这就要求我在复制excel模板的时候判断一下DataTable里记录的条数,我现在只能复制excel模板,可不知道如何动态添加模板中的sheet,如果一下代码则是添加3个空白sheet:
string strSheetName = mySheet.Name.ToString();
Excel.Worksheet beforeSheet = (Excel.Worksheet)myBook.Sheets.get_Item(strSheetName);
myBook.Worksheets.Add(Type.Missing,beforeSheet,3,oMissiong);
希望高手指点,谢谢大家!附上我已有的代码:
Excel.Application myApp;
Excel.Workbook myBook;
Excel.Worksheet mySheet;
Excel.Sheets mySheets;
FileInfo mode=new FileInfo(Application.StartupPath.Trim()+@"\施肥模板.xls");
mode.CopyTo(filename,true);
myApp=null;myBook = null;mySheet = null;
object oMissiong = System.Reflection.Missing.Value;
myApp = new Excel.ApplicationClass();
myApp.Visible=false;
myApp.Workbooks.Open(filename.Replace(".txt",".xls"),oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
myBook = myApp.Workbooks[1];
mySheets = myBook.Worksheets;
mySheet = (Excel.Worksheet)mySheets[1];
mySheet.Activate();
做好了一个excel模板,里面有一个sheet,我现在想把DataTable里面的数据导出到一个复制的excel里面,DataTable里面的一条纪录对应一个sheet,这就要求我在复制excel模板的时候判断一下DataTable里记录的条数,我现在只能复制excel模板,可不知道如何动态添加模板中的sheet,如果一下代码则是添加3个空白sheet:
string strSheetName = mySheet.Name.ToString();
Excel.Worksheet beforeSheet = (Excel.Worksheet)myBook.Sheets.get_Item(strSheetName);
myBook.Worksheets.Add(Type.Missing,beforeSheet,3,oMissiong);
希望高手指点,谢谢大家!附上我已有的代码:
Excel.Application myApp;
Excel.Workbook myBook;
Excel.Worksheet mySheet;
Excel.Sheets mySheets;
FileInfo mode=new FileInfo(Application.StartupPath.Trim()+@"\施肥模板.xls");
mode.CopyTo(filename,true);
myApp=null;myBook = null;mySheet = null;
object oMissiong = System.Reflection.Missing.Value;
myApp = new Excel.ApplicationClass();
myApp.Visible=false;
myApp.Workbooks.Open(filename.Replace(".txt",".xls"),oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
myBook = myApp.Workbooks[1];
mySheets = myBook.Worksheets;
mySheet = (Excel.Worksheet)mySheets[1];
mySheet.Activate();
reference:
http://www.codeproject.com/csharp/excel_using_oledb.asp
/// 在指定名称的工作表后面拷贝指定个数的该工作表的副本,并重命名
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <param name="sheetCount">工作表个数</param>
public void CopyWorkSheets(string sheetName,int sheetCount)
{
try
{
Excel.Worksheet sheet = null;
int sheetIndex = 0; for(int i=1;i<=this.WorkSheetCount;i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if(workSheet.Name == sheetName)
{
sheet = workSheet;
sheetIndex = workSheet.Index;
}
} if(sheet != null)
{
for(int i=sheetCount;i>=1;i--)
{
sheet.Copy(this.missing,sheet);
} //重命名
for(int i=sheetIndex;i<=sheetIndex+sheetCount;i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
workSheet.Name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
}
}
else
{
this.KillExcelProcess();
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch(Exception e)
{
this.KillExcelProcess();
throw e;
}
}
for (int k=1;k<=tempCount;k++)
{
mySheet = (Excel.Worksheet)myBook.Sheets.get_Item(k);
mySheet.Activate();
}