DataGridView生成CSV,XML 和 EXCEL文件

这里提到了使用DataGridView 去生成三种不同类型的文件，其实生成EXCEL我曾经遇到过三种，希望这些对广大开发者有帮助。并且当中我还列出一些如何通过xls导入到datagridview,很多使用该winform技术的同仁一定很熟悉这种方法。

1.首先是如何将数据从 xls文件加载到winform的DataGridView控件中。

[csharp] view plaincopyprint?
  1. private void button1_Click(object sender, EventArgs e)  
  2.        {  
  3.            OpenFileDialog fileDLG = new OpenFileDialog();  
  4.            fileDLG.Title = "Open Excel File";  
  5.            fileDLG.Filter = "Excel Files|*.xls;*.xlsx";  
  6.            fileDLG.InitialDirectory = @"C:\Users\...\Desktop\";  
  7.            if (fileDLG.ShowDialog() == DialogResult.OK)  
  8.            {  
  9.                string filename = System.IO.Path.GetFileName(fileDLG.FileName);  
  10.                string path = System.IO.Path.GetDirectoryName(fileDLG.FileName);  
  11.                excelLocationTB.Text = @path + "\\" + filename;  
  12.                string ExcelFile = @excelLocationTB.Text;  
  13.                if (!File.Exists(ExcelFile))  
  14.                    MessageBox.Show(String.Format("File {0} does not Exist", ExcelFile));  
  15.   
  16.                OleDbConnection theConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0;");  
  17.                theConnection.Open();  
  18.                OleDbDataAdapter theDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", theConnection);  
  19.                DataSet DS = new DataSet();  
  20.                theDataAdapter.Fill(DS, "ExcelInfo");  
  21.                dataGridView1.DataSource = DS.Tables["ExcelInfo"];  
  22.                formatDataGrid();  
  23.                MessageBox.Show("Excel File Loaded");  
  24.                toolStripProgressBar1.Value += 0;  
  25.            }  
  26.        }  
  27.              private void formatDataGrid()  
  28.           {  
  29.              dataGridView1.ColumnHeadersVisible = true;  
  30.              dataGridView1.Columns[0].Name = "Path Name";  
  31.              dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);  
  32.            }  
 private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDLG = new OpenFileDialog();
            fileDLG.Title = "Open Excel File";
            fileDLG.Filter = "Excel Files|*.xls;*.xlsx";
            fileDLG.InitialDirectory = @"C:\Users\...\Desktop\";
            if (fileDLG.ShowDialog() == DialogResult.OK)
            {
                string filename = System.IO.Path.GetFileName(fileDLG.FileName);
                string path = System.IO.Path.GetDirectoryName(fileDLG.FileName);
                excelLocationTB.Text = @path + "\\" + filename;
                string ExcelFile = @excelLocationTB.Text;
                if (!File.Exists(ExcelFile))
                    MessageBox.Show(String.Format("File {0} does not Exist", ExcelFile));

                OleDbConnection theConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0;");
                theConnection.Open();
                OleDbDataAdapter theDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", theConnection);
                DataSet DS = new DataSet();
                theDataAdapter.Fill(DS, "ExcelInfo");
                dataGridView1.DataSource = DS.Tables["ExcelInfo"];
                formatDataGrid();
                MessageBox.Show("Excel File Loaded");
                toolStripProgressBar1.Value += 0;
            }
        }
              private void formatDataGrid()
           {
              dataGridView1.ColumnHeadersVisible = true;
              dataGridView1.Columns[0].Name = "Path Name";
              dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
            }


 

2.接下来如何将DataGridView控件中的数据保存到CSV格式的文件中。

 

[csharp] view plaincopyprint?
  1. if (dataGridView1.Rows.Count == 0)  
  2.                   {  
  3.                       MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  4.                       return;  
  5.                   }  
  6.                   else  
  7.                   {  
  8.                       SaveFileDialog saveFileDialog = new SaveFileDialog();  
  9.                       saveFileDialog.Filter = "CSV files (*.csv)|*.csv";  
  10.                       saveFileDialog.FilterIndex = 0;  
  11.                       saveFileDialog.RestoreDirectory = true;  
  12.                       saveFileDialog.CreatePrompt = true;  
  13.                       saveFileDialog.FileName = null;  
  14.                       saveFileDialog.Title = "Save path of the file to be exported";  
  15.                       if (saveFileDialog.ShowDialog() == DialogResult.OK)  
  16.                       {  
  17.                           Stream myStream = saveFileDialog.OpenFile();  
  18.                           StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));  
  19.                           string strLine = "";  
  20.                           try  
  21.                           {  
  22.                               //Write in the headers of the columns.   
  23.                               for (int i = 0; i < dataGridView1.ColumnCount; i++)  
  24.                               {  
  25.                                   if (i > 0)  
  26.                                       strLine += ",";  
  27.                                   strLine += dataGridView1.Columns[i].HeaderText;  
  28.                               }  
  29.                               strLine.Remove(strLine.Length - 1);  
  30.                               sw.WriteLine(strLine);  
  31.                               strLine = "";  
  32.                               //Write in the content of the columns.   
  33.                               for (int j = 0; j < dataGridView1.Rows.Count; j++)  
  34.                               {  
  35.                                   strLine = "";  
  36.                                   for (int k = 0; k < dataGridView1.Columns.Count; k++)  
  37.                                   {  
  38.                                       if (k > 0)  
  39.                                           strLine += ",";  
  40.                                       if (dataGridView1.Rows[j].Cells[k].Value == null)  
  41.                                           strLine += "";  
  42.                                       else  
  43.                                       {  
  44.                                           string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();  
  45.                                           strLine += m.Replace(",", "，");  
  46.                                       }  
  47.                                   }  
  48.                                   strLine.Remove(strLine.Length - 1);  
  49.                                   sw.WriteLine(strLine);  
  50.                                   //Update the Progess Bar.   
  51.                                   toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;  
  52.                               }  
  53.                               sw.Close();  
  54.                               myStream.Close();  
  55.                               MessageBox.Show("Data has been exported to：" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  56.                               toolStripProgressBar1.Value = 0;  
  57.                           }  
  58.                           catch (Exception ex)  
  59.                           {  
  60.                               MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  61.                           }  
  62.                       }  
  63.                   }  
if (dataGridView1.Rows.Count == 0)
                  {
                      MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
                      return;
                  }
                  else
                  {
                      SaveFileDialog saveFileDialog = new SaveFileDialog();
                      saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
                      saveFileDialog.FilterIndex = 0;
                      saveFileDialog.RestoreDirectory = true;
                      saveFileDialog.CreatePrompt = true;
                      saveFileDialog.FileName = null;
                      saveFileDialog.Title = "Save path of the file to be exported";
                      if (saveFileDialog.ShowDialog() == DialogResult.OK)
                      {
                          Stream myStream = saveFileDialog.OpenFile();
                          StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                          string strLine = "";
                          try
                          {
                              //Write in the headers of the columns.
                              for (int i = 0; i < dataGridView1.ColumnCount; i++)
                              {
                                  if (i > 0)
                                      strLine += ",";
                                  strLine += dataGridView1.Columns[i].HeaderText;
                              }
                              strLine.Remove(strLine.Length - 1);
                              sw.WriteLine(strLine);
                              strLine = "";
                              //Write in the content of the columns.
                              for (int j = 0; j < dataGridView1.Rows.Count; j++)
                              {
                                  strLine = "";
                                  for (int k = 0; k < dataGridView1.Columns.Count; k++)
                                  {
                                      if (k > 0)
                                          strLine += ",";
                                      if (dataGridView1.Rows[j].Cells[k].Value == null)
                                          strLine += "";
                                      else
                                      {
                                          string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                                          strLine += m.Replace(",", "，");
                                      }
                                  }
                                  strLine.Remove(strLine.Length - 1);
                                  sw.WriteLine(strLine);
                                  //Update the Progess Bar.
                                  toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;
                              }
                              sw.Close();
                              myStream.Close();
                              MessageBox.Show("Data has been exported to：" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                              toolStripProgressBar1.Value = 0;
                          }
                          catch (Exception ex)
                          {
                              MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                          }
                      }
                  }


 

3.接下是如何保存到XML,这里提供了两种方法。

[csharp] view plaincopyprint?
  1.  Output to XML file format using StreamWriting Object.  
  2. if (dataGridView1.Rows.Count == 0)  
  3. {  
  4.     MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  5.     return;  
  6. }  
  7. else  
  8. {  
  9.     SaveFileDialog saveFileDialog = new SaveFileDialog();  
  10.     saveFileDialog.Filter = "XML files (*.xml)|*.xml";  
  11.     saveFileDialog.FilterIndex = 0;  
  12.     saveFileDialog.RestoreDirectory = true;  
  13.     saveFileDialog.CreatePrompt = true;  
  14.     saveFileDialog.FileName = null;  
  15.     saveFileDialog.Title = "Save path of the file to be exported";  
  16.     if (saveFileDialog.ShowDialog() == DialogResult.OK)  
  17.     {  
  18.         Stream myStream = saveFileDialog.OpenFile();  
  19.         StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));  
  20.         try  
  21.         {  
  22.             sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");  
  23.             sw.WriteLine("<NewXML>");  
  24.   
  25.             for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)  
  26.             {  
  27.                 sw.WriteLine("<Row" + i + ">");  
  28.                 for (int j = 0; j < dataGridView1.Columns.Count; j++)  
  29.                     sw.WriteLine("<" + dataGridView1.Columns[j].HeaderText + ">" + dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() + "</" + dataGridView1.Columns[j].HeaderText + ">");  
  30.                 sw.WriteLine("</Row" + i + ">");  
  31.                 //Update the Progess Bar.   
  32.                 toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);  
  33.             }  
  34.             sw.WriteLine("</NewXML>");  
  35.             sw.Close();  
  36.             myStream.Close();  
  37.             MessageBox.Show("Data has been exported to：" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  38.             toolStripProgressBar1.Value = 0;  
  39.         }  
  40.         catch (Exception ex)  
  41.         {  
  42.             MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  43.         }  
  44.     }  
  45. }  
                   Output to XML file format using StreamWriting Object.
                  if (dataGridView1.Rows.Count == 0)
                  {
                      MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
                      return;
                  }
                  else
                  {
                      SaveFileDialog saveFileDialog = new SaveFileDialog();
                      saveFileDialog.Filter = "XML files (*.xml)|*.xml";
                      saveFileDialog.FilterIndex = 0;
                      saveFileDialog.RestoreDirectory = true;
                      saveFileDialog.CreatePrompt = true;
                      saveFileDialog.FileName = null;
                      saveFileDialog.Title = "Save path of the file to be exported";
                      if (saveFileDialog.ShowDialog() == DialogResult.OK)
                      {
                          Stream myStream = saveFileDialog.OpenFile();
                          StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                          try
                          {
                              sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
                              sw.WriteLine("<NewXML>");

                              for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                              {
                                  sw.WriteLine("<Row" + i + ">");
                                  for (int j = 0; j < dataGridView1.Columns.Count; j++)
                                      sw.WriteLine("<" + dataGridView1.Columns[j].HeaderText + ">" + dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() + "</" + dataGridView1.Columns[j].HeaderText + ">");
                                  sw.WriteLine("</Row" + i + ">");
                                  //Update the Progess Bar.
                                  toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);
                              }
                              sw.WriteLine("</NewXML>");
                              sw.Close();
                              myStream.Close();
                              MessageBox.Show("Data has been exported to：" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                              toolStripProgressBar1.Value = 0;
                          }
                          catch (Exception ex)
                          {
                              MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                          }
                      }
                  }

上面是hard code写法，用XMLELEMENT也能达到同样目的。

 

[csharp] view plaincopyprint?
  1. if (dataGridView1.Rows.Count == 0)  
  2.                  {  
  3.                      MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  4.                      return;  
  5.                  }  
  6.                  else  
  7.                  {  
  8.                      SaveFileDialog saveFileDialog = new SaveFileDialog();  
  9.                      saveFileDialog.Filter = "XML files (*.xml)|*.xml";  
  10.                      saveFileDialog.FilterIndex = 0;  
  11.                      saveFileDialog.RestoreDirectory = true;  
  12.                      saveFileDialog.CreatePrompt = true;  
  13.                      saveFileDialog.FileName = null;  
  14.                      saveFileDialog.Title = "Save path of the file to be exported";  
  15.                      if (saveFileDialog.ShowDialog() == DialogResult.OK)  
  16.                      {  
  17.                          string str = saveFileDialog.FileName;  
  18.                         XmlDocument doc = new XmlDocument();  
  19.                          XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);  
  20.                               doc.AppendChild(docNode);  
  21.   
  22.                            // Create and insert a new element.   
  23.                            XmlNode rowsNode = doc.CreateElement("NewXML");  
  24.                            doc.AppendChild(rowsNode);  
  25.   
  26.                          try  
  27.                          {  
  28.                              for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)  
  29.                              {  
  30.                                  XmlNode rowNode = doc.CreateElement("row" + i);  
  31.   
  32.   
  33.                                  for (int j = 0; j < dataGridView1.Columns.Count; j++)  
  34.                                  {  
  35.   
  36.                                      XmlAttribute rowAttribute = doc.CreateAttribute(dataGridView1.Columns[j].HeaderText);  
  37.                                        rowAttribute.Value =dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() ;  
  38.                                        rowNode.Attributes.Append(rowAttribute);  
  39.                                        rowsNode.AppendChild(rowNode);  
  40.                                  }  
  41.                                  //Update the Progess Bar.   
  42.                                  toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);  
  43.   
  44.                              }  
  45.                              doc.Save(str);  
  46.   
  47.                          }  
  48.                          catch (Exception ex)  
  49.                          {  
  50.                              MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  51.                          }  
  52.                      }  
  53.                  }  
 if (dataGridView1.Rows.Count == 0)
                  {
                      MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
                      return;
                  }
                  else
                  {
                      SaveFileDialog saveFileDialog = new SaveFileDialog();
                      saveFileDialog.Filter = "XML files (*.xml)|*.xml";
                      saveFileDialog.FilterIndex = 0;
                      saveFileDialog.RestoreDirectory = true;
                      saveFileDialog.CreatePrompt = true;
                      saveFileDialog.FileName = null;
                      saveFileDialog.Title = "Save path of the file to be exported";
                      if (saveFileDialog.ShowDialog() == DialogResult.OK)
                      {
                          string str = saveFileDialog.FileName;
                         XmlDocument doc = new XmlDocument();
                          XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
                               doc.AppendChild(docNode);

                            // Create and insert a new element.
                            XmlNode rowsNode = doc.CreateElement("NewXML");
                            doc.AppendChild(rowsNode);

                          try
                          {
                              for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                              {
                                  XmlNode rowNode = doc.CreateElement("row" + i);


                                  for (int j = 0; j < dataGridView1.Columns.Count; j++)
                                  {

                                      XmlAttribute rowAttribute = doc.CreateAttribute(dataGridView1.Columns[j].HeaderText);
                                        rowAttribute.Value =dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() ;
                                        rowNode.Attributes.Append(rowAttribute);
                                        rowsNode.AppendChild(rowNode);
                                  }
                                  //Update the Progess Bar.
                                  toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);

                              }
                              doc.Save(str);

                          }
                          catch (Exception ex)
                          {
                              MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                          }
                      }
                  }


3.最后提供保存到EXCEL的三种方法，第一种是hardcode,第二种是openxml pacage,第三种是传统的操作Excl interp的貌似是 Automatic方法。

[csharp] view plaincopyprint?
  1. if (dataGridView1.Rows.Count == 0)  
  2. {  
  3.     MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  4.     return;  
  5. }  
  6. else  
  7. {  
  8.     SaveFileDialog saveFileDialog = new SaveFileDialog();  
  9.     saveFileDialog.Filter = "Execl files (*.xls)|*.xls";  
  10.     saveFileDialog.FilterIndex = 0;  
  11.     saveFileDialog.RestoreDirectory = true;  
  12.     saveFileDialog.CreatePrompt = true;  
  13.     saveFileDialog.FileName = null;  
  14.     saveFileDialog.Title = "Save path of the file to be exported";  
  15.     if (saveFileDialog.ShowDialog() == DialogResult.OK)  
  16.     {  
  17.         Stream myStream = saveFileDialog.OpenFile();  
  18.         StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));  
  19.         string strLine = "";  
  20.         try  
  21.         {  
  22.             //Write in the headers of the columns.   
  23.             for (int i = 0; i < dataGridView1.ColumnCount; i++)  
  24.             {  
  25.                 if (i > 0)  
  26.                     strLine += Convert.ToChar(9);  
  27.                 strLine += dataGridView1.Columns[i].HeaderText;  
  28.             }  
  29.             strLine.Remove(strLine.Length - 1);  
  30.             sw.WriteLine(strLine);  
  31.             strLine = "";  
  32.             //Write in the content of the columns.   
  33.             for (int j = 0; j < dataGridView1.Rows.Count; j++)  
  34.             {  
  35.                 strLine = "";  
  36.                 for (int k = 0; k < dataGridView1.Columns.Count; k++)  
  37.                 {  
  38.                     if (k > 0)  
  39.                         strLine += Convert.ToChar(9);  
  40.                     if (dataGridView1.Rows[j].Cells[k].Value == null)  
  41.                         strLine += "";  
  42.                     else  
  43.                     {  
  44.                         string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();  
  45.                         strLine += m.Replace(",", "，");  
  46.                     }  
  47.                 }  
  48.                 strLine.Remove(strLine.Length - 1);  
  49.                 sw.WriteLine(strLine);  
  50.                 //Update the Progess Bar.   
  51.                 toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;  
  52.             }  
  53.             sw.Close();  
  54.             myStream.Close();  
  55.             MessageBox.Show("Data has been exported to：" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  56.             toolStripProgressBar1.Value = 0;  
  57.         }  
  58.         catch (Exception ex)  
  59.         {  
  60.             MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  61.         }  
  62.     }  
  63. }  
                      if (dataGridView1.Rows.Count == 0)
                      {
                          MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
                          return;
                      }
                      else
                      {
                          SaveFileDialog saveFileDialog = new SaveFileDialog();
                          saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
                          saveFileDialog.FilterIndex = 0;
                          saveFileDialog.RestoreDirectory = true;
                          saveFileDialog.CreatePrompt = true;
                          saveFileDialog.FileName = null;
                          saveFileDialog.Title = "Save path of the file to be exported";
                          if (saveFileDialog.ShowDialog() == DialogResult.OK)
                          {
                              Stream myStream = saveFileDialog.OpenFile();
                              StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                              string strLine = "";
                              try
                              {
                                  //Write in the headers of the columns.
                                  for (int i = 0; i < dataGridView1.ColumnCount; i++)
                                  {
                                      if (i > 0)
                                          strLine += Convert.ToChar(9);
                                      strLine += dataGridView1.Columns[i].HeaderText;
                                  }
                                  strLine.Remove(strLine.Length - 1);
                                  sw.WriteLine(strLine);
                                  strLine = "";
                                  //Write in the content of the columns.
                                  for (int j = 0; j < dataGridView1.Rows.Count; j++)
                                  {
                                      strLine = "";
                                      for (int k = 0; k < dataGridView1.Columns.Count; k++)
                                      {
                                          if (k > 0)
                                              strLine += Convert.ToChar(9);
                                          if (dataGridView1.Rows[j].Cells[k].Value == null)
                                              strLine += "";
                                          else
                                          {
                                              string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                                              strLine += m.Replace(",", "，");
                                          }
                                      }
                                      strLine.Remove(strLine.Length - 1);
                                      sw.WriteLine(strLine);
                                      //Update the Progess Bar.
                                      toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;
                                  }
                                  sw.Close();
                                  myStream.Close();
                                  MessageBox.Show("Data has been exported to：" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                  toolStripProgressBar1.Value = 0;
                              }
                              catch (Exception ex)
                              {
                                  MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                              }
                          }
                      }


Automatic：

[csharp] view plaincopyprint?
  1. int iRows = 0;  
  2. int iCols = 0;  
  3. int iTrueCols = 0;  
  4. Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();  
  5. Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);  
  6. Microsoft.Office.Interop.Excel.Worksheet ws = null;  
  7.   
  8. if (wb.Worksheets.Count > 0)  
  9. {  
  10.     ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);  
  11. }  
  12. else  
  13. {  
  14.     wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);  
  15.     ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);  
  16. }  
  17.   
  18. if (ws != null)  
  19. {  
  20.     ws.Name = "SheetName";  
  21.   
  22.     iRows = dataGridView1.Rows.Count;      //加上列头行     
  23.     iTrueCols = dataGridView1.Columns.Count;   //包含隐藏的列，一共有多少列     
  24.   
  25.     //求列数，省略Visible = false的列     
  26.     for (int i = 0; i < dataGridView1.Columns.Count; i++)  
  27.     {  
  28.         if (dataGridView1.Columns[i].Visible) iCols++;  
  29.     }  
  30.   
  31.     string[,] dimArray = new string[iRows + 1, iCols];  
  32.   
  33.     for (int j = 0, k = 0; j < iTrueCols; j++)  
  34.     {  
  35.         //省略Visible = false的列     
  36.         if (dataGridView1.Columns[j].Visible)  
  37.         {  
  38.             dimArray[0, k] = dataGridView1.Columns[j].HeaderText;  
  39.             k++;  
  40.         }  
  41.     }  
  42.   
  43.     for (int i = 0; i < iRows; i++)  
  44.     {  
  45.         for (int j = 0, k = 0; j < iTrueCols; j++)  
  46.         {  
  47.             //省略Visible = false的列     
  48.             if (dataGridView1.Columns[j].Visible)  
  49.             {  
  50.                 if (dataGridView1.Rows[i].Cells[j].Value != null)  
  51.                     dimArray[i + 1, k] = dataGridView1.Rows[i].Cells[j].Value.ToString();  
  52.                 else  
  53.                     dimArray[i + 1, k] = "";  
  54.                 k++;  
  55.   
  56.             }  
  57.   
  58.         }  
  59.   
  60.     }  
  61.   
  62.   
  63.   
  64.   
  65.     ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Value2 = dimArray;  
  66.     ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Bold = true;  
  67.     ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Size = 10.0;  
  68.     ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).RowHeight = 14.25;  
  69.     //ws.Columns[.ColumnWidth = datagridview.Columns[0].Width;     
  70.     for (int j = 0, k = 0; j < iTrueCols; j++)  
  71.     {  
  72.         //省略Visible = false的列     
  73.         if (dataGridView1.Columns[j].Visible)  
  74.         {  
  75.             ws.get_Range(ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range).ColumnWidth = (dataGridView1.Columns[j].Width / 8.4) > 255 ? 255 : (dataGridView1.Columns[j].Width / 8.4);  
  76.             //ws.Columns.c = datagridview.Columns[j].Width;     
  77.             k++;  
  78.         }  
  79.     }  
  80. }  
  81. app.Visible = true;  
                          int iRows = 0;
                          int iCols = 0;
                          int iTrueCols = 0;
                          Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                          Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);
                          Microsoft.Office.Interop.Excel.Worksheet ws = null;

                          if (wb.Worksheets.Count > 0)
                          {
                              ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
                          }
                          else
                          {
                              wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                              ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
                          }

                          if (ws != null)
                          {
                              ws.Name = "SheetName";

                              iRows = dataGridView1.Rows.Count;      //加上列头行  
                              iTrueCols = dataGridView1.Columns.Count;   //包含隐藏的列，一共有多少列  

                              //求列数，省略Visible = false的列  
                              for (int i = 0; i < dataGridView1.Columns.Count; i++)
                              {
                                  if (dataGridView1.Columns[i].Visible) iCols++;
                              }

                              string[,] dimArray = new string[iRows + 1, iCols];

                              for (int j = 0, k = 0; j < iTrueCols; j++)
                              {
                                  //省略Visible = false的列  
                                  if (dataGridView1.Columns[j].Visible)
                                  {
                                      dimArray[0, k] = dataGridView1.Columns[j].HeaderText;
                                      k++;
                                  }
                              }

                              for (int i = 0; i < iRows; i++)
                              {
                                  for (int j = 0, k = 0; j < iTrueCols; j++)
                                  {
                                      //省略Visible = false的列  
                                      if (dataGridView1.Columns[j].Visible)
                                      {
                                          if (dataGridView1.Rows[i].Cells[j].Value != null)
                                              dimArray[i + 1, k] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                                          else
                                              dimArray[i + 1, k] = "";
                                          k++;

                                      }

                                  }

                              }




                              ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Value2 = dimArray;
                              ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Bold = true;
                              ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Size = 10.0;
                              ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).RowHeight = 14.25;
                              //ws.Columns[.ColumnWidth = datagridview.Columns[0].Width;  
                              for (int j = 0, k = 0; j < iTrueCols; j++)
                              {
                                  //省略Visible = false的列  
                                  if (dataGridView1.Columns[j].Visible)
                                  {
                                      ws.get_Range(ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range).ColumnWidth = (dataGridView1.Columns[j].Width / 8.4) > 255 ? 255 : (dataGridView1.Columns[j].Width / 8.4);
                                      //ws.Columns.c = datagridview.Columns[j].Width;  
                                      k++;
                                  }
                              }
                          }
                          app.Visible = true;


 

OpenXml： 

 

 

 

string myStream = saveFileDialog.FileName;

 

                         

                          CreateSpreadsheetWorkbook(myStream,dvtodt(dataGridView1));

 

string myStream = saveFileDialog.FileName;

 

                         

                          CreateSpreadsheetWorkbook(myStream,dvtodt(dataGridView1));

 

先将DataGridView转换为DataTable,在操作：

[csharp] view plaincopyprint?
  1. public static void CreateSpreadsheetWorkbook(string filepath, DataTable dt)  
  2.           {  
  3.               
  4.   
  5.               // Create a spreadsheet document by supplying the filepath.   
  6.               // By default, AutoSave = true, Editable = true, and Type = xlsx.   
  7.               FileInfo fileInfo = new FileInfo(filepath);  
  8.               if (fileInfo.Exists)  
  9.                   fileInfo.Delete();  
  10.   
  11.               SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);  
  12.   
  13.               // Add a WorkbookPart to the document.   
  14.               WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();  
  15.               workbookPart.Workbook = new Workbook();  
  16.   
  17.               // Add a WorksheetPart to the WorkbookPart.   
  18.               WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();  
  19.               worksheetPart.Worksheet = new Worksheet(new SheetData());  
  20.   
  21.               // Add a workStylesPart to the workbookPart.   
  22.               WorkbookStylesPart workStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();  
  23.               workStylesPart.Stylesheet = new Stylesheet();  
  24.   
  25.               // Add a Stylesheet to the workStylesPart.   
  26.               WorkbookStylesPart workbookStylesPart = spreadsheetDocument.WorkbookPart.WorkbookStylesPart;  
  27.               Stylesheet styleSheet = workbookStylesPart.Stylesheet;  
  28.   
  29.               // Add Fonts and other some information.   
  30.               Fonts fonts = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };  
  31.   
  32.               // Add based font to Fonts.   
  33.               Font font = new Font();  
  34.               FontSize fontSize = new FontSize() { Val = 11D };  
  35.               Color color = new Color() { Theme = (UInt32Value)1U };  
  36.               FontName fontName = new FontName() { Val = "Calibri" };  
  37.               FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = 2 };  
  38.               FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };  
  39.               font.Append(fontSize);  
  40.               font.Append(color);  
  41.               font.Append(fontName);  
  42.               font.Append(fontFamilyNumbering);  
  43.               font.Append(fontScheme);  
  44.   
  45.               // Add fontSpecial to Fonts.   
  46.               Font fontSpecial = new Font();  
  47.               Bold bold = new Bold();  
  48.               FontSize fontSizeSpecial = new FontSize() { Val = 11D };  
  49.               Color colorSpecial = new Color() { Theme = (UInt32Value)1U };  
  50.               FontName fontNameSpecial = new FontName() { Val = "Calibri" };  
  51.               FontFamilyNumbering fontFamilyNumberingSpecial = new FontFamilyNumbering() { Val = 2 };  
  52.               FontScheme fontSchemeSpecial = new FontScheme() { Val = FontSchemeValues.Minor };  
  53.               fontSpecial.Append(bold);  
  54.               fontSpecial.Append(fontSizeSpecial);  
  55.               fontSpecial.Append(colorSpecial);  
  56.               fontSpecial.Append(fontNameSpecial);  
  57.               fontSpecial.Append(fontFamilyNumberingSpecial);  
  58.               fontSpecial.Append(fontSchemeSpecial);  
  59.   
  60.               // Add font and fontSpecial to fonts collection.   
  61.               fonts.Append(font);  
  62.               fonts.Append(fontSpecial);  
  63.   
  64.               // Fills.When the object is serialized out as xml, its qualified name is x:fills.   
  65.               Fills fills = new Fills() { Count = (UInt32Value)2U };  
  66.               Fill fill = new Fill();  
  67.   
  68.               // Pattern.When the object is serialized out as xml, its qualified name is x:patternFill.   
  69.               PatternFill patternFill = new PatternFill() { PatternType = PatternValues.None };  
  70.               fill.Append(patternFill);  
  71.   
  72.               // This patternFillSpecial served for fillSpecial.   
  73.               Fill fillSpecial = new Fill();  
  74.               PatternFill patternFillSpecial = new PatternFill() { PatternType = PatternValues.Gray125 };  
  75.               fillSpecial.Append(patternFillSpecial);  
  76.   
  77.               // Add fill and fillSpecial to fills collection.   
  78.               fills.Append(fill);  
  79.               fills.Append(fillSpecial);  
  80.   
  81.               // Borders.When the object is serialized out as xml, its qualified name is x:borders.   
  82.               Borders borders = new Borders() { Count = (UInt32Value)1U };  
  83.               Border border = new Border();  
  84.   
  85.               // Left Border.When the object is serialized out as xml, its qualified name is x:left.   
  86.               LeftBorder leftBorder = new LeftBorder();  
  87.   
  88.               // Right Border.When the object is serialized out as xml, its qualified name is x:right.   
  89.               RightBorder rightBorder = new RightBorder();  
  90.   
  91.               //Top Border.When the object is serialized out as xml, its qualified name is x:top.   
  92.               TopBorder topBorder = new TopBorder();  
  93.   
  94.               // Bottom Border.When the object is serialized out as xml, its qualified name is x:bottom.   
  95.               BottomBorder bottomBorder = new BottomBorder();  
  96.   
  97.               // This element specifies the color and line style for the diagonal border(s) of a cell,   
  98.               // possibly including diagonally up and diagonally down.   
  99.               DiagonalBorder diagonalBorder = new DiagonalBorder();  
  100.   
  101.               // Add leftBorder, rightBorder,topBorder,bottomBorder and diagonalBorder into border.   
  102.               border.Append(leftBorder);  
  103.               border.Append(rightBorder);  
  104.               border.Append(topBorder);  
  105.               border.Append(bottomBorder);  
  106.               border.Append(diagonalBorder);  
  107.               borders.Append(border);  
  108.   
  109.               // This is the critical object when the sample want to bold the cell format.   
  110.               CellStyleFormats cellStyleFormats = new CellStyleFormats() { Count = (UInt32Value)1U };  
  111.               CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };  
  112.   
  113.               // Add cellFormat to cellStyleFormats   
  114.               cellStyleFormats.Append(cellFormat);  
  115.               CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)2U };  
  116.               CellFormat cellFormatSpecial = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };  
  117.               CellFormat cellFormatAnotherSpecial = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };  
  118.               cellFormats.Append(cellFormatSpecial);  
  119.               cellFormats.Append(cellFormatAnotherSpecial);  
  120.               CellStyles cellStyles = new CellStyles() { Count = (UInt32Value)1U };  
  121.               CellStyle cellStyle = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };  
  122.               cellStyles.Append(cellStyle);  
  123.               DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)0U };  
  124.               TableStyles tableStyles = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };  
  125.               StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();  
  126.               StylesheetExtension stylesheetExtension = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };  
  127.               stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");  
  128.               stylesheetExtensionList.Append(stylesheetExtension);  
  129.   
  130.               // Add fonts,fills,borders,cellStyleFormats,cellFormats,cellStyles,differentialFormats,tableStyles and stylesheetEntensionList to styleSheet   
  131.               styleSheet.Append(fonts);  
  132.               styleSheet.Append(fills);  
  133.               styleSheet.Append(borders);  
  134.               styleSheet.Append(cellStyleFormats);  
  135.               styleSheet.Append(cellFormats);  
  136.               styleSheet.Append(cellStyles);  
  137.               styleSheet.Append(differentialFormats);  
  138.               styleSheet.Append(tableStyles);  
  139.               styleSheet.Append(stylesheetExtensionList);  
  140.   
  141.               // Save the result.   
  142.               styleSheet.Save();  
  143.   
  144.               // Add Sheets to the Workbook.   
  145.               Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());  
  146.   
  147.               // Append a new worksheet and associate it with the workbook.   
  148.               Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };  
  149.               sheets.Append(sheet);  
  150.               string cl = "";  
  151.               uint row = 2;  
  152.               int index;  
  153.               Cell cell;  
  154.   
  155.               // fill data into Excel file   
  156.               foreach (DataRow dr in dt.Rows)  
  157.               {  
  158.                   for (int idx = 0; idx < dt.Columns.Count; idx++)  
  159.                   {  
  160.                       if (idx >= 26)  
  161.                           cl = "A" + Convert.ToString(Convert.ToChar(65 + idx - 26));  
  162.                       else  
  163.                           cl = Convert.ToString(Convert.ToChar(65 + idx));  
  164.   
  165.                       SharedStringTablePart shareStringPart;  
  166.                       if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)  
  167.                       {  
  168.                           shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();  
  169.                       }  
  170.                       else  
  171.                       {  
  172.                           shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();  
  173.                       }  
  174.                       if (row == 2)  
  175.                       {  
  176.                           index = InsertSharedStringItem(dt.Columns[idx].ColumnName, shareStringPart);  
  177.                           cell = InsertCellInWorksheet(cl, row - 1, worksheetPart);  
  178.                           cell.CellValue = new CellValue(index.ToString());  
  179.                           cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);  
  180.                       }  
  181.   
  182.                       // Insert the text into the SharedStringTablePart.   
  183.                       index = InsertSharedStringItem(dr[idx].ToString(), shareStringPart);  
  184.                       cell = InsertCellInWorksheet(cl, row, worksheetPart);  
  185.                       cell.CellValue = new CellValue(index.ToString());  
  186.                       cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);  
  187.                   }  
  188.   
  189.                   row++;  
  190.               }  
  191.   
  192.               // Save the workbookPart.   
  193.               workbookPart.Workbook.Save();  
  194.   
  195.               // Close the document.   
  196.               spreadsheetDocument.Close();  
  197.           }  
  198.              
  199.   
  200.           /// <summary>   
  201.           /// Insert SharedStringItem object.   
  202.           /// </summary>   
  203.           private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)  
  204.           {  
  205.   
  206.               // If the part does not contain a SharedStringTable, create one.   
  207.               if (shareStringPart.SharedStringTable == null)  
  208.               {  
  209.                   shareStringPart.SharedStringTable = new SharedStringTable();  
  210.               }  
  211.               int i = 0;  
  212.   
  213.               // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.   
  214.               foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())  
  215.               {  
  216.                   if (item.InnerText == text)  
  217.                   {  
  218.                       return i;  
  219.                   }  
  220.                   i++;  
  221.               }  
  222.   
  223.               // The text does not exist in the part. Create the SharedStringItem and return its index.   
  224.               shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));  
  225.               shareStringPart.SharedStringTable.Save();  
  226.               return i;  
  227.           }  
  228.   
  229.           /// <summary>   
  230.           /// Insert cell in worksheet.   
  231.           /// </summary>   
  232.           private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)  
  233.           {  
  234.               Worksheet worksheet = worksheetPart.Worksheet;  
  235.               SheetData sheetData = worksheet.GetFirstChild<SheetData>();  
  236.               string cellReference = columnName + rowIndex;  
  237.   
  238.               // If the worksheet does not contain a row with the specified row index, insert one.   
  239.               Row row;  
  240.               if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)  
  241.               {  
  242.                   row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();  
  243.               }  
  244.               else  
  245.               {  
  246.                   row = new Row() { RowIndex = rowIndex };  
  247.                   sheetData.Append(row);  
  248.               }  
  249.   
  250.               // If there is not a cell with the specified column name, insert one.    
  251.               if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)  
  252.               {  
  253.                   return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();  
  254.               }  
  255.               else  
  256.               {  
  257.   
  258.                   // Cells must be in sequential order according to CellReference.   
  259.                   // Determine where to insert the new cell.   
  260.                   Cell refCell = null;  
  261.                   Cell newCell = new Cell() { CellReference = cellReference };  
  262.                   row.InsertBefore(newCell, refCell);  
  263.                   worksheet.Save();  
  264.                   return newCell;  
  265.   
  266.               }  
  267.           } 
posted on 2012-10-25 13:30  酒歌  阅读(414)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 酒歌
Powered by .NET 8.0 on Kubernetes