如何将datagrid控件绑定的数据导出到excel中

Question

我是初学者，请问怎样才能将datagrid中的数据导出 并保存到excel中，最好能按照设定的格式来显示

Answer 1

public static void ToExcel(System.Web.UI.Control ctl,string FileName)
        {
            HttpContext.Current.Response.Charset ="UTF-8";
            HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
            HttpContext.Current.Response.ContentType ="application/ms-excel";
            HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+""+FileName+".xls");
            ctl.Page.EnableViewState =false;
            System.IO.StringWriter  tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            ctl.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        } ToExcel(DataGrid1,"dgExcel");

Answer 2

http://www.mzedu.com/这个网站上有详细的解说！

Answer 3

如果我想设置excel的格式有没有这方面的代码

Answer 4

/// 
/// 读取Excel文档
/// 
/// 文件名称
/// 返回一个数据集
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open(); 
string strExcel = ""; 
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel="select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,"table1"); 
return ds;
}
/// 
/// 写入Excel文档
/// 
/// 文件名称
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open(); 
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
//cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
//cmd.ExecuteNonQuery ();
for(int i=0;i {
if(fp2.Sheets [0].Cells[i,0].Text!="")
{
cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
cmd.ExecuteNonQuery ();
}
}
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
}
return false;
}

Answer 5

我是初学者，请问怎样才能将datagrid中的数据导出 并保存到excel中，最好能按照设定的格式来显示
-------------------------------
1：利用excel编程接口对象
2：利用水晶报表导出。

Answer 6

//操作excel文件
Excel.Application excel = new Excel.ApplicationClass();
excel.DefaultFilePath   = System.Windows.Forms.Application.StartupPath+@"\file";
Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
///画线
string cell = "A"+k.ToString()+":"+((char)(j+63)).ToString()+(i-1).ToString();
Excel.Range range = excel.get_Range(cell,Type.Missing);
range.Select();
Excel.Borders borders=range.Borders;
borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle   = Excel.XlLineStyle.xlLineStyleNone;borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeLeft].Weight    = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic;borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeTop].Weight    = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic;borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeBottom].Weight    = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic;borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeRight].Weight    = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic;borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight    = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic;borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideVertical].Weight    = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic;
///画线结束
//调整格式range = excel.get_Range("A1:F1",Type.Missing);
range.Select();
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment   = Excel.XlVAlign.xlVAlignBottom;
range.WrapText = false;
range.Orientation = 0;
range.AddIndent = false;
range.ShrinkToFit = false;
range.MergeCells  = false;
range.Merge(Type.Missing);
//保存
workbook.SaveAs(name+"("+currentTime.ToLongDateString()+")",Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange,Excel.XlSaveConflictResolution.xlLocalSessionChanges,Type.Missing,Type.Missing,Type.Missing);
workbook.Close(false,Type.Missing,Type.Missing);