DataGridView生成CSV,XML 和 EXCEL文件

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

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.            } 
    

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

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.                   } 
    

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

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. }