整理的 C# DataSet导出至Excel文件（2）

 整理的 C# DataSet导出至Excel文件       
        //方法1
        public bool SavetoExcel(string Path, DataSet ds)
        {
            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;

                int RowCount = ds.Tables[0].Rows.Count;
                string strfiles = "";

                for (int i = 1; i <= ds.Tables[0].Columns.Count; i++)
                {
                    strfiles = strfiles + "A" + i.ToString() + " ,";
                }
                for (int i = 0; i < RowCount; i++)
                {
                    string strValues = "";
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        strValues = strValues + " '" + ds.Tables[0].Rows[i][j].ToString() + "' ,";
                    }

                    //cmd.CommandText = "INSERT INTO [sheet1$]  VALUES("+strValues.TrimEnd(',') +" )";

                    cmd.CommandText = "INSERT INTO [sheet1$](" + strfiles.TrimEnd(',') + ") select " + strValues.TrimEnd(',') + " ";
                    cmd.ExecuteNonQuery();

                }
                conn.Close();
                cmd.Dispose();
                return true;
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            }
            return false;
        }
         * */
        #endregion

        //方法2 导出 EXCEL 文件
        protected void ExportExcel(string strExportFilename, DataSet dsResultData)
        {
            if (dsResultData == null) return;

            string saveFileName = strExportFilename;

            Excel.Application xlApp = new Excel.Application();
            object missing = System.Reflection.Missing.Value;

            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            // string oldCaption = Title_label.Text.Trim();
            long totalCount = dsResultData.Tables[0].Rows.Count;

            for (int i = 0; i < dsResultData.Tables[0].Columns.Count; i++)
            {
                int IndexCol = arrColumn.IndexOf(dsResultData.Tables[0].Columns[i].Caption );
                if (IndexCol > -1)
                {
                    worksheet.Cells[1, i + 1] = arrColumnName[IndexCol ];// dsResultData.Tables[0].Columns[i].ColumnName;
                }
            }

            //写入数值
            // System.Windows.Forms.VisualStyles.VisualStyleElement.Window.Caption.Visible = true;
            for (int r = 0; r < dsResultData.Tables[0].Rows.Count; r++)
            {
                for (int i = 0; i < dsResultData.Tables[0].Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dsResultData.Tables[0].Rows[r][i].ToString();
                }
              
                Application.DoEvents();
            }
            worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            //worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing);

            //this.Caption.Visible = false;
            //this.Caption.Text = oldCaption;

            //range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dsResultData.Tables[0].Rows.Count + 2, dsResultData.Tables[0].Columns.Count]);
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;

            //if (dsResultData.Tables[0].Columns.Count > 1)
            //{
            //    range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            //}
            workbook.Close(missing, missing, missing);
            xlApp.Quit();
        }

-------------------------

给自己淘宝小店做个广告：新上品质超好的婴幼儿用品，大家快来看看哦，针对老顾客全部进价销售！
http://lovehealthylife.taobao.com/

主营孕妇装，防辐射装，婴幼儿用品，自用送人都很合适！虽然没有搞折扣优惠之类的活动，但绝对质优价廉！比搞活动的还便宜！你懂滴