1,將DataGridView中的資料匯入到Csv檔案中
public static bool dataGridViewToCSV(DataGridView dataGridView)
{
if (dataGridView.Rows.Count == 0)
{
MessageBox.Show("沒有數據可導出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.FileName = null;
saveFileDialog.Title = "保存";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Stream stream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(stream, System.Text.Encoding.GetEncoding(-0));
string strLine = "";
try
{
//表頭
for (int i = 0; i < dataGridView.ColumnCount; i++)
{
if (i > 0)
strLine += ",";
strLine += dataGridView.Columns[i].HeaderText;
}
strLine.Remove(strLine.Length - 1);
sw.WriteLine(strLine);
strLine = "";
//表的內容
for (int j = 0; j < dataGridView.Rows.Count; j++)
{
strLine = "";
int colCount = dataGridView.Columns.Count;
for (int k = 0; k < colCount; k++)
{
if (k > 0 && k < colCount)
strLine += ",";
if (dataGridView.Rows[j].Cells[k].Value == null)
strLine += "";
else
{
string cell = dataGridView.Rows[j].Cells[k].Value.ToString().Trim();
//防止裡面含有特殊符號
cell = cell.Replace("\"", "\"\"");
cell = "\"" + cell + "\"";
strLine += cell;
}
}
sw.WriteLine(strLine);
}
sw.Close();
stream.Close();
MessageBox.Show("數據被導出到:" + saveFileDialog.FileName.ToString(), "導出完畢", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "導出錯誤", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
}
return true;
}
2,C#中將DataGridView中的資料匯出到Excel中
public static bool dataGridViewToExcel(DataGridView dataGridView)
{
if (dataGridView.Rows.Count == 0)
{
MessageBox.Show("沒有數據可導出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
return false; //被點了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("無法創建Excel對象,您的電腦可能未安裝Excel");
return false;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//寫入標題
for (int i = 0; i < dataGridView.ColumnCount; i++)
{ worksheet.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText; }
//寫入數值
for (int r = 0; r < dataGridView.Rows.Count; r++)
{
for (int i = 0; i < dataGridView.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列寬自適應
MessageBox.Show(fileName + "資料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("導出文件時出錯,文件可能正被打開!\n" + ex.Message);
}
}
xlApp.Quit();
Kill(xlApp);
GC.Collect();//強行銷毀
return true;
}
//************************************************ *********************************************//
//*功能:當使用Excel.Application方法的時候,系統會自動創建一個Excel進程,即使當你使用Excel.Quit()之後,也是不會關閉的。 //*參數:通過進程ID來唯一標識我們自己創建的進程,然後在關閉Excel.Application的時候一同將進程Kill掉。 //*返回值:無//*時間:2018-04-25 //******************************* ************************************************** *************// public static void Kill(Microsoft.Office.Interop.Excel.Application excel) { IntPtr t = new IntPtr(excel.Hwnd); int k = 0; GetWindowThreadProcessId( t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); }