将数据库中数据导入Excel中的问题！求助！！必结贴！

Question

情况描述：
导出的Excel有模板要求如图图中红色部分应该为从SQL中读出的数据写入的位置，其他为固定的模板，请问应该怎样实现？

Answer 1

固定的东西代码写死就可以了
你可以参考一下下面的 比你的需求简单  /// <summary>
        /// 导出数据到excel
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="ReportTitle"></param>
        public static void ExportDataGridToExcel(DataTable myTable, string ReportTitle)
        {
            //DataTable myTable = (DataTable)grid.DataSource;            try
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();                int rowIndex;
                int colIndex;                rowIndex = 1;
                colIndex = 0;                Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);                List<Microsoft.Office.Interop.Excel.Range> lirange = new List<Microsoft.Office.Interop.Excel.Range>();
                Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[2, 7], xlApp.Cells[100, 13]);
                //Range("A1:A1").SelectSelection.NumberFormatLocal   =   "@"   
                range.NumberFormatLocal = "@";// 设置单元格格式为货币格式 
                //Microsoft.Office.Interop.Excel.Range range1 = xlApp.get_Range(xlApp.Cells[3, 10], xlApp.Cells[3, myTable.Columns.Count]);
                ////Range("A1:A1").SelectSelection.NumberFormatLocal   =   "@"   
                //range1.NumberFormatLocal = "@";// 设置单元格格式为货币格式 
                for (int i = 3; i < myTable.Rows.Count; i++)
                {                    Microsoft.Office.Interop.Excel.Range range1 = xlApp.get_Range(xlApp.Cells[i, 7], xlApp.Cells[i, 13]);
                    //Range("A1:A1").SelectSelection.NumberFormatLocal   =   "@"   
                    range1.NumberFormatLocal = "@";// 设置单元格格式为货币格式 
                    lirange.Add(range1);
                }
                //xlApp.ActiveCell.d
                //xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
                //xlApp.ActiveCell.Font.Size = 18;
                //xlApp.ActiveCell.Font.Bold = true;                //将表中的栏位名称填到Excel的第一行
                for (int i = 1; i < myTable.Columns.Count; i++)
                {
                    colIndex = colIndex + 1;
                    //myTable.Columns[i].DataType = typeof(string);
                    xlApp.Cells[1, colIndex] = myTable.Columns[i].ColumnName;
                }                //得到的表所有行,赋值给单元格
                for (int row = 0; row < myTable.Rows.Count; row++)
                {
                    rowIndex = rowIndex + 1;
                    colIndex = 0;
                    for (int col = 1; col < myTable.Columns.Count; col++)
                    {
                        colIndex = colIndex + 1;
                        if (myTable.Rows[row][col].ToString() == "==")
                            xlApp.Cells[rowIndex, colIndex] = " == ";// myTable.Rows[row][col].ToString();
                        else
                        {
                            xlApp.Cells[rowIndex, colIndex] = myTable.Rows[row][col].ToString();
                        }
                    }
                }                //xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
                //xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;                xlApp.Cells.EntireColumn.AutoFit();
                xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;                xlApp.Visible = true;
            }
            catch (Exception e)
            {
                throw e;
            }        }

Answer 2

    protected void ToExcel_Click(object sender, System.EventArgs e)
    {
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=Sheet1.xls");
        Response.Charset = "gb2312";
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView1.Columns[13].Visible = false;
        GridView1.Columns[14].Visible = false;
        GridView1.AllowPaging = false;
      //  InitData();
        Query();
        GridView1.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
        GridView1.AllowPaging = true;
        InitData();
        Query();
    }
    protected void paging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
    }    public override void VerifyRenderingInServerForm(Control control)
    { }

Answer 3

你娃娃运气好！Boolean falg = true;
            //建立一个Excel.Application的新进程
           
            
            Excel.Application app = new Excel.Application();          
            app.Visible = false;
            app.UserControl = true;
            Workbooks workbooks = app.Workbooks;
            string path = Server.MapPath("Execl") + "\\★省发安全核查表.xls";
            Workbook workbook = workbooks.Add(path);//这里的Add方法里的参数就是模板的路径
            Sheets sheets = workbook.Worksheets;
            Worksheet worksheet = (Worksheet)sheets.get_Item(1);//模板只有一个sheet表
                   
            #region  表头信息
            worksheet.Cells[21, 3] = dss.Tables["sdzjbxx"].Rows[0]["shi"];
            worksheet.Cells[22, 3] = dss.Tables["sdzjbxx"].Rows[0]["xian"];
            worksheet.Cells[23, 3] = dss.Tables["sdzjbxx"].Rows[0]["zhen"];
            worksheet.Cells[19, 3] = dss.Tables["sdzjbxx"].Rows[0]["sdzjbxxID"];
            worksheet.Cells[20, 3] = dss.Tables["sdzjbxx"].Rows[0]["name"];

Answer 4

打开excel模板写入数据
public void INSERT_Excel(DataView dvs,string strPath,string Name)
{
Excel.Application app=new Application();
Excel._Workbook book;
Excel._Worksheet sheet;
book=(Excel._Workbook)app.Workbooks.Open(strPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
sheet=(Excel._Worksheet)book.Sheets[1];
int j=dvs.Count;
Excel.Range ran1=app.ActiveCell;
ran1=sheet.get_Range(sheet.Cells[1,1],sheet.Cells[1,9]);
ran1.Value2=Name;
for(int i=0;i<dvs.Count;i++)
{
try
{
sheet.Cells[i+4,"A"]=""                         sheet.Cells[i+4,"B"]="";
}
catch(Exception ex)
{
HttpContext.Current.Response.Write("<script language='javascript'>alert('"+ex.Message+"')</script>");
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
HttpContext.Current.Response.Write("<script language='javascript'>alert('导出失败！')</script>");
return;
}
  
} 
book.Save();
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
GC.Collect();
GC.Collect();
HttpContext.Current.Response.Write("<script language='javascript'>alert('导出成功！')</script>");

HttpContext.Current.Response.Write("<script language='javascript'>window.open('../Template_temp/A.xls','_bank')</script>");
}