單表頭，多表頭匯出excel

標籤：style   blog   http   color   檔案   資料   

一、單表頭

 1  #region 匯出 2             if (this.dgvInfo.Rows.Count > 0) 3             { 4                 SaveFileDialog saveFileDialog = new SaveFileDialog(); 5                 saveFileDialog.DefaultExt = "xls"; 6                 saveFileDialog.Filter = "EXCEL檔案(*.XLS)|*.xls"; 7                 saveFileDialog.FilterIndex = 0; 8                 saveFileDialog.FileName = "單據明細"; 9                 saveFileDialog.RestoreDirectory = true;10                 saveFileDialog.CreatePrompt = true;11                 saveFileDialog.Title = "匯出到EXCEL";12                 saveFileDialog.ShowDialog();13                 if (saveFileDialog.FileName == "")14                     return;15                 Stream myStream;16                 myStream = saveFileDialog.OpenFile();17                 StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));18                 string str = "";19                 try20                 {21                     for (int i = 0; i < this.dgvInfo.ColumnCount; i++)22                     {23                         if (i > 0)24                         {25                             str += "\t";26                         }27                         str += dgvInfo.Columns[i].HeaderText;28                     }29                     sw.WriteLine(str);30                     for (int j = 0; j < dgvInfo.Rows.Count; j++)31                     {32                         string tempStr = "";33                         for (int k = 0; k < dgvInfo.Columns.Count; k++)34                         {35                             if (k > 0)36                             {37                                 tempStr += "\t";38                             }39                             if (dgvInfo.Rows[j].Cells[k].Value == null)40                             {41                                 dgvInfo.Rows[j].Cells[k].Value = "";42                             }43                             tempStr += dgvInfo.Rows[j].Cells[k].Value.ToString();44                         }45                         sw.WriteLine(tempStr);46                     }47                     sw.Close();48                     myStream.Close();49                 }50                 catch (Exception ex)51                 {52                     MessageBox.Show(ex.ToString());53                 }54                 finally55                 {56                     sw.Close();57                     myStream.Close();58                 }59             }60             #endregion

二、表多頭

 1             try 2             { 3                 if (treedgv.Rows.Count > 0) 4                 { 5                     string sFile = System.Windows.Forms.Application.StartupPath; 6  7                     //Missing 在System.Reflection命名空間下。 8                     //string sFileSave = sFile + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + "一天一業績.xls"; 9 10                     SaveFileDialog saveFileDialog = new SaveFileDialog();11                     saveFileDialog.DefaultExt = "xls";12                     saveFileDialog.Filter = "EXCEL檔案(*.XLS)|*.xls";13                     saveFileDialog.FilterIndex = 0;14                     saveFileDialog.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "支付方式";15                     saveFileDialog.RestoreDirectory = true;16                     saveFileDialog.CreatePrompt = true;17                     saveFileDialog.Title = "匯出到EXCEL";18                     saveFileDialog.ShowDialog();19                     if (saveFileDialog.FileName == "")20                         return;21 22                     //需要添加 Microsoft.Office.Interop.Excel引用 23                     Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();24 25                     app.Visible = false;26                     app.UserControl = true;27                     Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;28                     Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(sFile + "\\支付方式.xls"); //載入模板29                     Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;30                     Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //第一個工作薄。31                     if (worksheet == null)32                         return;  //工作薄中沒有工作表.33 34                     ////寫入資料，Excel索引從1開始。35 36                     for (int i = 1; i <= treedgv.Rows.Count; i++)37                     {38                         int row_ = 3 + i;  //Excel模板上表頭和標題列佔了2行,根據實際模板需要修改;39                         int dt_row = i - 1; //dataTable的行是從0開始的。 40                         //worksheet.Cells[row_, 1] = i.ToString();41                         for (int j = 1; j <= treedgv.Columns.Count; j++)42                         {43                             if (treedgv.Rows[0].Cells["Column" + j].Value.ToString() != "")44                             {45                                 worksheet.Cells[row_, j] = treedgv.Rows[i-1].Cells["Column" + j].Value.ToString();46                             }47                         }48 49                     }50 51 52                     workbook.SaveAs(saveFileDialog.FileName, 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);53 54 55                     workbook.Close(null, null, null);56                     workbooks.Close();57                     worksheet = null;58                     app.Quit();59                     app = null;60                 }61             }62             catch (Exception)63             {64 65                 MessageBox.Show("匯出失敗！", "異常");66             }