导入导出excel(.xls,.xlsx)代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】导入导出excel(.xls,.xlsx)
作者:玉云 / 发布于2014/10/22/ 817

	#region 导入导出excel

	 private SaveFileDialog SaveDialog;
	 private OpenFileDialog OpenDialog;
	 private Thread InvokeThread;
	 private DialogResult InvokeResult;
	 bool issave = false;
	 private void Invoker()
	 {

	 if (issave)
	 SaveDialog = new SaveFileDialog();
	 else
	 OpenDialog = new OpenFileDialog();
	 InvokeThread = new Thread(new ThreadStart(InvokeMethod));
	 InvokeThread.SetApartmentState(ApartmentState.STA);
	 InvokeResult = DialogResult.None;
	 }

	 private DialogResult InvokeDialogResult()
	 {
	 InvokeThread.Start();
	 InvokeThread.Join();
	 return InvokeResult;
	 }

	 private void InvokeMethod()
	 {
	 if (issave)
	 InvokeResult = SaveDialog.ShowDialog();
	 else
	 InvokeResult = OpenDialog.ShowDialog();

	 }

	 /// <summary>
	 /// //excel 导入datatable
	 /// </summary>
	 /// <param name="bn">导入按钮</param>
	 /// <param name="process">当前操作进度信息</param>
	 /// <param name="error">错误信息</param>
	 /// <returns></returns>
	 public System.Data.DataTable ImportExcel(System.Windows.Forms.Button bn, System.Windows.Forms.TextBox process, System.Windows.Forms.TextBox error)
	 {
	 process.Text = "";
	 error.Text = "";
	 bn.Enabled = false;
	 issave = false;
	 Invoker();
	 OpenDialog.Filter = "Excel(2000-2007) (*.xls)|*.xls|Excel(2010) (*.xlsx)|*.xlsx";
	 OpenDialog.FilterIndex = 0;
	 OpenDialog.RestoreDirectory = true;
	 //OpenDialog.Title = "导入文件路径";
	 System.Data.DataTable dt = null;
	 if (InvokeDialogResult() == DialogResult.OK)
	 {
	 string strName = OpenDialog.FileName;
	 string strcon = "";
	 try
	 {
	 FileInfo file = new FileInfo(strName);
	 if (!file.Exists)
	 {
	 error.Text = GetErrorMSG("D0014", "");
	 return null;
	 }
	 string extension = file.Extension;
	 //不同版本的连接字符串
	 switch (extension)
	 {
	 case ".xls":
	 //strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
	 //break;
	 case ".xlsx":
	 strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
	 break;
	 default:
	 strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
	 break;
	 }
	 OleDbConnection olecon = new OleDbConnection(strcon);
	 olecon.Open();
	 //返回Excel的架构，包括各个sheet表的名称等　
	 System.Data.DataTable dtSheetName = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
	 string[] strTableNames = new string[dtSheetName.Rows.Count];
	 for (int k = 0; k < dtSheetName.Rows.Count; k++)
	 {
	 strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
	 OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + strTableNames[k] + "]", strcon);
	 dt = new System.Data.DataTable();
	 da.Fill(dt);
	 if (dt.Rows.Count != 0)
	 {
	 bn.Enabled = true;
	 olecon.Close();
	 return dt;
	 }
	 }
	 }
	 catch (Exception ex)
	 {
	 bn.Enabled = true;
	 error.Text = ex.Message;
	 }
	 }
	 bn.Enabled = true;
	 return dt;
	 }

	
	 /// <summary>
	 /// DataGridView 导出到Excel
	 /// </summary>
	 /// <param name="bn">导出按钮</param>
	 /// <param name="gridView"></param>
	 /// <param name="process">当前操作进度信息</param>
	 /// <param name="error">错误信息</param>
	 /// <param name="sheetname">sheet名称</param>
	 public void ExportToExcel(System.Windows.Forms.Button bn, DataGridView gridView, System.Windows.Forms.TextBox process, System.Windows.Forms.TextBox error, string sheetname)
	 {
	 //导出到execl
	 try
	 {
	 process.Text = "";
	 error.Text = "";
	 issave = true;
	 Invoker();
	 SaveDialog.Filter = "Excel(2000-2007) (*.xls)|*.xls|Excel(2010) (*.xlsx)|*.xlsx";
	 SaveDialog.FilterIndex = 0;
	 SaveDialog.RestoreDirectory = true;
	 SaveDialog.Title = "导出文件保存路径";
	 if (InvokeDialogResult() == DialogResult.OK)
	 {
	 bn.Enabled = false;
	 string strName = SaveDialog.FileName;
	 if (strName.Length != 0)
	 {
	 //没有数据的话就不往下执行
	 if (gridView.Rows.Count == 0)
	 {
	 bn.Enabled = true;
	 error.Text = GetErrorMSG("D0015", "");
	 return;
	 }
	 //ProgressBar toolStripProgressBar1=new ProgressBar();
	 //toolStripProgressBar1.Visible = true;

	 System.Reflection.Missing miss = System.Reflection.Missing.Value;
	 Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
	 excel.Application.Workbooks.Add(true); ;
	 excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
	 excel.SheetsInNewWorkbook = 1;//只有一个sheet
	 if (excel == null)
	 {
	 bn.Enabled = true;
	 error.Text = GetErrorMSG("D0016", "");
	 return;
	 }
	 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
	 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
	 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
	 sheet.Name = sheetname;

	 System.Data.DataTable dt = new System.Data.DataTable();
	 for (int i = 0; i < gridView.Columns.Count; i++)
	 {
	 //if (gridView.Columns[i].Name == "cb" || !gridView.Columns[i].Visible)
	 //{
	 // gridView.Columns.Remove(gridView.Columns[i]);
	 // i--;
	 //}
	 if (gridView.Columns[i].Name != "cb" && gridView.Columns[i].Visible)
	 {
	 dt.Columns.Add(gridView.Columns[i].HeaderText);
	 }
	 }
	 for (int i = 0; i < gridView.Rows.Count; i++)
	 {
	 System.Data.DataRow dr = dt.NewRow();
	 for (int j = 0; j < dt.Columns.Count; j++)
	 {
	 if (gridView.Rows[i].Cells[dt.Columns[j].Caption].Value.GetType() == typeof(string))
	 {
	 dr[j] = "'" + Functions.GetStringValue(gridView.Rows[i].Cells[dt.Columns[j].Caption].Value);
	 }
	 else if (gridView.Rows[i].Cells[dt.Columns[j].Caption].Value.GetType() == typeof(DateTime))
	 {
	 dr[j] = "'" + Convert.ToDateTime(gridView.Rows[i].Cells[dt.Columns[j].Caption].Value).ToString("yyyy/MM/dd HH:mm");
	 }
	 else
	 {
	 dr[j] = Functions.GetStringValue(gridView.Rows[i].Cells[dt.Columns[j].Caption].Value);
	 }
	 }
	 dt.Rows.Add(dr);
	 }
	 //生成列名称
	 //for (int i = 0; i < gridView.Columns.Count; i++)
	 //{
	 // excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
	 //}

	 for (int i = 0; i < dt.Columns.Count; i++)
	 {
	 excel.Cells[1, i + 1] = dt.Columns[i].Caption;
	 }
	 float percent = 0;
	 int count = 0;

	 for (int i = 0; i < dt.Rows.Count; i++)
	 {
	 for (int j = 0; j < dt.Columns.Count; j++)
	 {
	 //if (dt.Rows[i][j].GetType() == typeof(string))
	 //{
	 // excel.Cells[i + 2, j + 1] = "'" + dt.Rows[i][j].ToString();
	 //}
	 //else if (dt.Rows[i][j].GetType() == typeof(DateTime))
	 //{
	 // excel.Cells[i + 2, j + 1] = "'" + Convert.ToDateTime(dt.Rows[i][j]).ToString("yyyy/MM/dd HH:mm");
	 //}
	 //else
	 //{
	 excel.Cells[i + 2, j + 1] = Functions.GetStringValue(dt.Rows[i][j]);
	 //}
	 //自动换行
	 //excel.get_Range(excel.Cells[i + 2, j + 1], excel.Cells[j + 1, j + 1]).Columns.WrapText = true;
	 //自动加行高
	 //excel.get_Range(excel.Cells[i + 2, j + 1], excel.Cells[i + 2, j + 1]).Rows.AutoFit();
	 //System.Windows.Forms.Application.DoEvents();

	 }

	 //toolStripProgressBar1.Value += 100 / gridView.RowCount;
	 count++;
	 percent = ((float)(100 * count)) / dt.Rows.Count;
	 process.Text = GetErrorMSG("D0017", "") + "[" + percent.ToString("0.00") + "%]...";

	 }

	 //填充数据
	 //for (int i = 0; i < gridView.Rows.Count; i++)
	 //{
	 // for (int j = 0; j < gridView.Columns.Count; j++)
	 // {
	 // if (gridView[j, i].Value.GetType() == typeof(string))
	 // {
	 // excel.Cells[i + 2, j + 1] = "'" + gridView[j, i].Value.ToString();
	 // }
	 // else if (gridView[j, i].Value.GetType() == typeof(DateTime))
	 // {
	 // excel.Cells[i + 2, j + 1] = "'" + Convert.ToDateTime(gridView[j, i].Value).ToString("yyyy/MM/dd HH:mm");
	 // }
	 // else
	 // {
	 // excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
	 // }
	 // //自动换行
	 // //excel.get_Range(excel.Cells[i + 2, j + 1], excel.Cells[j + 1, j + 1]).Columns.WrapText = true;
	 // //自动加行高
	 // //excel.get_Range(excel.Cells[i + 2, j + 1], excel.Cells[i + 2, j + 1]).Rows.AutoFit();
	 // //System.Windows.Forms.Application.DoEvents();

	 // }

	 // //toolStripProgressBar1.Value += 100 / gridView.RowCount;
	 // count++;
	 // percent = ((float)(100 * count)) / gridView.Rows.Count;
	 // process.Text = GetErrorMSG("D0017", "") + "[" + percent.ToString("0.00") + "%]...";

	 //}
	 sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
	 book.Close(false, miss, miss);
	 books.Close();
	 excel.Quit();
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
	 bn.Enabled = true;
	 GC.Collect();
	 process.Text = GetErrorMSG("D0018", "");
	 //toolStripProgressBar1.Value = 0;
	 //toolStripProgressBar1.Visible = false;
	 System.Diagnostics.Process.Start(strName);
	 }
	 }

	 }
	 catch (Exception ex)
	 {
	 error.Text = ex.Message;
	 bn.Enabled = true;
	 }
	 }

	
	 /// <summary>
	 /// datatable 导出到Excel
	 /// </summary>
	 /// <param name="bn">导出按钮</param>
	 /// <param name="gridView"></param>
	 /// <param name="process">当前操作进度信息</param>
	 /// <param name="error">错误信息</param>
	 /// <param name="sheetname">sheet名称</param>
	 public void DataTableToExcel(System.Windows.Forms.Button bn, System.Data.DataTable dt, System.Windows.Forms.TextBox process, System.Windows.Forms.TextBox error, string sheetname)
	 {
	 //导出到execl
	 try
	 {
	 process.Text = "";
	 error.Text = "";
	 issave = true;
	 Invoker();
	 SaveDialog.Filter = "Excel(2000-2007) (*.xls)|*.xls|Excel(2010) (*.xlsx)|*.xlsx";
	 SaveDialog.FilterIndex = 0;
	 SaveDialog.RestoreDirectory = true;
	 SaveDialog.Title = "导出文件保存路径";
	 if (InvokeDialogResult() == DialogResult.OK)
	 {
	 bn.Enabled = false;
	 string strName = SaveDialog.FileName;
	 if (strName.Length != 0)
	 {
	 //没有数据的话就不往下执行
	 if (dt.Rows.Count == 0)
	 {
	 bn.Enabled = true;
	 error.Text = GetErrorMSG("D0015", "");
	 return;
	 }
	 System.Reflection.Missing miss = System.Reflection.Missing.Value;
	 Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
	 excel.Application.Workbooks.Add(true); ;
	 excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
	 excel.SheetsInNewWorkbook = 1;//只有一个sheet
	 if (excel == null)
	 {
	 bn.Enabled = true;
	 error.Text = GetErrorMSG("D0016", "");
	 return;
	 }
	 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
	 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
	 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
	 sheet.Name = sheetname;
	 //生成列名称
	 for (int i = 0; i < dt.Columns.Count; i++)
	 {
	 excel.Cells[1, i + 1] = dt.Columns[i].Caption;
	 }
	 float percent = 0;
	 int count = 0;

	 for (int i = 0; i < dt.Rows.Count; i++)
	 {
	 for (int j = 0; j < dt.Columns.Count; j++)
	 {
	 excel.Cells[i + 2, j + 1] = Functions.IsNull(dt.Rows[i][j]) ? "" : "'" + dt.Rows[i][j].ToString();
	 }
	 count++;
	 percent = ((float)(100 * count)) / dt.Rows.Count;
	 process.Text = GetErrorMSG("D0017", "") + "[" + percent.ToString("0.00") + "%]...";

	 }
	 sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
	 book.Close(false, miss, miss);
	 books.Close();
	 excel.Quit();
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
	 bn.Enabled = true;
	 GC.Collect();
	 process.Text = GetErrorMSG("D0018", "");
	 System.Diagnostics.Process.Start(strName);
	 }
	 }

	 }
	 catch (Exception ex)
	 {
	 error.Text = ex.Message;
	 bn.Enabled = true;
	 }
	 }

	
	 /// <summary>
	 /// datatable 导出到Excel (excel模板导出)
	 /// </summary>
	 /// <param name="bn">导出按钮</param>
	 /// <param name="gridView"></param>
	 /// <param name="process">当前操作进度信息</param>
	 /// <param name="error">错误信息</param>
	 /// <param name="sheetname">sheet名称</param>
	 public void CLToExcel(System.Windows.Forms.Button bn, System.Data.DataTable dt, System.Windows.Forms.TextBox process, System.Windows.Forms.TextBox error, string sheetname,string date)
	 {
	 //导出到execl
	 try
	 {
	 process.Text = "";
	 error.Text = "";
	 //调用的模板文件
	 string path = "";
	#if DEBUG
	 path = @"..\..\Data\材料导出.xlsx";
	#else
	 path=Application.StartupPath + @"\Data\材料导出.xlsx";
	#endif
	 FileInfo mode = new FileInfo(path);
	 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
	 if (app == null)
	 {
	 return;
	 }
	 app.Application.DisplayAlerts = false;
	 app.Visible = false;
	 if (mode.Exists)
	 {
	 Microsoft.Office.Interop.Excel.Workbook tworkbook;
	 Object missing = System.Reflection.Missing.Value;
	 app.Workbooks.Add(missing);
	 //调用模板
	 tworkbook = app.Workbooks.Open(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
	 Microsoft.Office.Interop.Excel.Worksheet tworksheet = (Microsoft.Office.Interop.Excel.Worksheet)tworkbook.Sheets[1];
	 DateTime time = Convert.ToDateTime(date);
	 //配置文件中显示的日期数
	 int defaultday = Functions.GetInt(GetNode("/configuration/DayOfWeek/day"));
	 for (int i = 0; i < defaultday; i++)
	 {
	 tworksheet.Cells[1, i + 7] = time.AddDays(i);
	 }
	 for (int i = 0; i < dt.Rows.Count; i++)
	 {
	 tworksheet.Cells[i + 3, 1] = "'" + Functions.GetStringValue(dt.Rows[i][0]);
	 tworksheet.Cells[i + 3, 2] = "'" + Functions.GetStringValue(dt.Rows[i][1]);
	 tworksheet.Cells[i + 3, 7] = "'" + Functions.GetStringValue(dt.Rows[i][2]);
	 }
	 issave = true;
	 Invoker();
	 SaveDialog.Filter = "Excel(2000-2007) (*.xls)|*.xls|Excel(2010) (*.xlsx)|*.xlsx";
	 SaveDialog.FilterIndex = 0;
	 SaveDialog.RestoreDirectory = true;
	 SaveDialog.Title = "导出文件保存路径";
	 if (InvokeDialogResult() == DialogResult.OK)
	 {
	 bn.Enabled = false;
	 string strName = SaveDialog.FileName;
	 if (strName.Length != 0)
	 {
	 tworksheet.SaveAs(strName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing);
	 tworkbook.Close(false, mode.FullName, missing);
	 app.Workbooks.Close();
	 app.Quit();
	 bn.Enabled = true;
	 tworkbook = null;
	 app = null;
	 GC.Collect();
	 process.Text = GetErrorMSG("D0018", "");
	 System.Diagnostics.Process.Start(strName);
	 }
	 }
	 }

	 }
	 catch (Exception ex)
	 {
	 error.Text = ex.Message;
	 bn.Enabled = true;
	 }
	 }
	 #endregion
试试其它关键字
同语言下
可能有用的
玉云贡献的其它代码(22)