送分100 求解难题

Question

在C#.net中怎么将ACCESS数据库表的数据转换成EXCEL表呢？谢谢啦！

Answer 1

        public static void DataTable2Excel(System.Data.DataTable dtData)
        {
            System.Web.UI.WebControls.DataGrid dgExport = null;
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;            if (dtData != null)
            {
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
                curContext.Response.Charset = "";                strWriter = new System.IO.StringWriter();
                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);                dgExport = new System.Web.UI.WebControls.DataGrid();
                dgExport.DataSource = dtData.DefaultView;
                dgExport.AllowPaging = false;
                dgExport.DataBind();                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }
先读到DATATABLE

Answer 2

楼上的方案是最通用的方法，把任何一种数据库类型读到DataTable后再转换为另一种数据库类型，在数据库不是非常大的情况下应该是合适的

Answer 3

public static void DataTableToExcel(DataTable dataTable1, string fileName)
        {
            
            System.IO.StreamWriter excelDoc;            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n<Workbook " +
                  "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
                  " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
                  "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
                  "office:spreadsheet\">\r\n <Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
                  "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
                  "\r\n <Protection/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
                  "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
                  "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
                  " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"Decimal\">\r\n <NumberFormat " +
                  "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
                  "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
                  "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
                  "</Styles>\r\n ";
            const string endExcelXML = "</Workbook>";            int rowCount = 0;
            int sheetCount = 1;            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");            //写标题 
            for (int i = 0; i < dataTable1.Columns.Count; i++)
            {                
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(dataTable1.Columns[i].ColumnName);
                excelDoc.Write("</Data></Cell>");
            }            excelDoc.Write("</Row>");
            for (int j = 0; j < dataTable1.Rows .Count ; j++)
            {
                rowCount++;
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>");                for (int k = 0; k < dataTable1.Columns.Count; k++)
                {                    
                    string XMLstring = "";
                    Object o = dataTable1.Rows[j][k];
                    if (o != null)
                    {
                        System.Type rowType;
                        rowType = o.GetType();
                        switch (rowType.ToString())
                        {
                            case "System.String":
                                XMLstring = o.ToString();
                                XMLstring = XMLstring.Trim();
                                XMLstring = XMLstring.Replace("&", "&");
                                XMLstring = XMLstring.Replace(">", ">");
                                XMLstring = XMLstring.Replace("<", "<");
                                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                               "<Data ss:Type=\"String\">");
                                excelDoc.Write(XMLstring);
                                excelDoc.Write("</Data></Cell>");
                                break;

Answer 4

case "System.DateTime":
                                DateTime XMLDate = (DateTime)o;
                                string XMLDatetoString = ""; //Excel Converted Date
                                XMLDatetoString = XMLDate.Year.ToString() +
                                     "-" +
                                     (XMLDate.Month < 10 ? "0" +
                                     XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                     "-" +
                                     (XMLDate.Day < 10 ? "0" +
                                     XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                     "T" +
                                     (XMLDate.Hour < 10 ? "0" +
                                     XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                     ":" +
                                     (XMLDate.Minute < 10 ? "0" +
                                     XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                     ":" +
                                     (XMLDate.Second < 10 ? "0" +
                                     XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                     ".000";
                                excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                             "<Data ss:Type=\"DateTime\">");
                                excelDoc.Write(XMLDatetoString);
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Boolean":
                                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                            "<Data ss:Type=\"String\">");
                                excelDoc.Write(o.ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                        "<Data ss:Type=\"Number\">");
                                excelDoc.Write(o.ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Decimal":
                            case "System.Double":
                                excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                      "<Data ss:Type=\"Number\">");
                                excelDoc.Write(o.ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.DBNull":
                                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                      "<Data ss:Type=\"String\">");
                                excelDoc.Write("");
                                excelDoc.Write("</Data></Cell>");
                                break;
                            default:
                                throw (new Exception(rowType.ToString() + " not handled."));
                        }
                    }
                    else
                    {
                        XMLstring = XMLstring.Trim();
                        XMLstring = XMLstring.Replace("&", "&");
                        XMLstring = XMLstring.Replace(">", ">");
                        XMLstring = XMLstring.Replace("<", "<");
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                       "<Data ss:Type=\"String\">");
                        excelDoc.Write(XMLstring);
                        excelDoc.Write("</Data></Cell>");
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }

Answer 5

那如果是Winform 程序那应该怎么转换呢??

Answer 6

事实上Excel文件你也可以当作是一个可直接操作的数据对象，如果是Winform 程序，你可以参考一下这个文章：
http://www.qqread.com/csharp/p306090.html

Answer 7

先讀到dataset中，然後插入到excel中

Answer 8

jerryfos(想飞) ( ) 
连接中的文章
/// <summary> 
/// 读取Excel文档 
/// </summary>  
/// <param name="Path">文件名称</param> 
/// <returns>返回一个数据集</returns> 
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; 
}  /// <summary> 
/// 写入Excel文档 
/// </summary> 
/// <param name="Path">文件名称</param> 
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<fp2.Sheets [0].RowCount -1;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; 
}请问：
if(fp2.Sheets [0].Cells[i,0].Text!="") 
中的fp2应该怎么写定义（声明？）？