.net 根据模板导入excel

protected void ToExcel_Click(object sender, ImageClickEventArgs e)
    {
       
        Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        if (app == null)
            return;
        app.Visible = false;
        app.UserControl = true;
        Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
        Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Server.MapPath("~/ExcelTemplet/Info.xls"));//加载模板
        Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
        Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //第一个工作簿
        if (worksheet == null)
            return;

        //获取数据
        DataTable dt = ts.DataSource;

        int rowCount=dt.Rows.Count;
        if (rowCount < 1)
            return;
　　　　　　　//写入数据，Excel索引从1开始
　　　　　　　worksheet.Cells[excelRow, 1] = dt.Rows[i]["cBAddr_Name"].ToString();
　　　　　　　//省略...

        }

        //Missing 在System.Reflection命名空间下
        string savePath = "~/ExcelTemplet/Temp/T1_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        workbook.SaveAs(Server.MapPath(savePath), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        //按顺序释放资源
        NAR(worksheet);
        NAR(sheets);
        NAR(workbook);
        NAR(workbooks);
        app.Quit();
        NAR(app);
        Response.Redirect(savePath);
    }
    private void NAR(object o)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
        }
        catch { }
        finally
        {
            o = null;
        }
    }

        

在固定行上或下插入新行

Excel.Range range = (Excel.Range)worksheet.Rows[6, Type.Missing];

range.Rows.Insert(Excel.XlDirection.xlDown, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);

合并多个Excel中的Sheet 到一个Excel

protected void Page_Load(object sender, EventArgs e)
    {
        Excel.Application app = new Excel.Application();

        Excel.Workbooks ws = app.Workbooks;

        Excel.Workbook workbook = ws.Add(Excel.XlWBATemplate.xlWBATWorksheet);//创建一个新的workbook
        //Excel.Workbook workbook = ws.Add(Server.MapPath("~/Work/Excel/Template/gkzb.xls")); //从现有文件加载

        //打开要合并的Excel
        Excel.Workbook wb1 = app.Workbooks.Open(Server.MapPath("~/1.xls"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        Excel.Workbook wb2 = app.Workbooks.Open(Server.MapPath("~/2.xls"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //拿出要合并的worksheet
        Excel.Worksheet wsheet1 = (Excel.Worksheet)wb1.Worksheets.get_Item(1);
        Excel.Worksheet wsheet2 = (Excel.Worksheet)wb2.Worksheets.get_Item(1);
        //要并入的Excel文件的worksheet
        Excel.Worksheet wsheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
        //复制要合并的worksheet到Excel本身的worksheet后
        wsheet1.Copy(Type.Missing, wsheet);
        wsheet2.Copy(Type.Missing, wsheet);

        string savePath = "~/123.xls";
        
        workbook.SaveAs(Server.MapPath(savePath), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        NAR(workbook);
        wb1.Close(false, Type.Missing, Type.Missing);
        wb2.Close(false, Type.Missing, Type.Missing);
        app.Quit();
        NAR(app);

    }