导出excel

StringBuilder sb = new StringBuilder(); //标题 sb.Append("MAC地址,生产商,报备厂商,终端类型,硬件版本,软件版本kernel,软件版本APP,上线IP,开机时间\n"); //内容 for (int i = 0; i < itemCount; i++) { sb.Append(((Label)rptList.Items[i].FindControl("lblMac")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblVendor")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblCom")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblBoxType")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblHw")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblKernel")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblSw")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblIP")).Text + ","); sb.Append(((Label)rptList.Items[i].FindControl("lblAddDate")).Text); sb.Append("\n"); } Response.ClearHeaders(); Response.AppendHeader("Content-disposition", "attachment;filename=开机认证记录.csv"); Response.Write(sb.ToString()); Response.End();

 

以下还有4种方法，不过有点麻烦：

1.采用流方式

private void DataGridViewToExcel(DataGridView dgv) { SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Execl files (*.xls)|*.xls"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.CreatePrompt = true; dlg.Title = "保存为Excel文件"; if (dlg.ShowDialog() == DialogResult.OK) { Stream myStream; myStream = dlg.OpenFile(); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string columnTitle = ""; try { //写入列标题 for (int i = 0; i < dgv.ColumnCount; i ) { if (i > 0) { columnTitle = "\t"; } columnTitle = dgv.Columns[i].HeaderText; } sw.WriteLine(columnTitle); //写入列内容 for (int j = 0; j < dgv.Rows.Count; j ) { string columnValue = ""; for (int k = 0; k < dgv.Columns.Count; k ) { if (k > 0) { columnValue = "\t"; } if (dgv.Rows[j].Cells[k].Value == null) columnValue = ""; else columnValue = dgv.Rows[j].Cells[k].Value.ToString().Trim(); } sw.WriteLine(columnValue); } sw.Close(); myStream.Close(); } catch (Exception e) { MessageBox.Show(e.ToString()); } finally { sw.Close(); myStream.Close(); } } }

 

2.显示Excel界面

/// <remarks> /// 从工具箱中添加"Microsoft Excel 11.0 Object Library" /// 命名空间添加 using Excel = Microsoft.Office.Interop.Excel; /// </remarks> /// <returns> </returns> public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle) { if (dgv.Rows.Count == 0) return false; //建立Excel对象 Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = isShowExcle; //生成字段名称 for (int i = 0; i < dgv.ColumnCount; i ) { excel.Cells[1, i 1] = dgv.Columns[i].HeaderText; } //填充数据 for (int i = 0; i < dgv.RowCount - 1; i ) { for (int j = 0; j < dgv.ColumnCount; j ) { if (dgv[j, i].ValueType == typeof(string)) { excel.Cells[i 2, j 1] = "'" dgv[j, i].Value.ToString(); } else { excel.Cells[i 2, j 1] = dgv[j, i].Value.ToString(); } } } return true; }

 

3.采用Excel控件方法

/// <summary> ///方法，导出DataGridView中的数据到Excel文件 /// </summary> /// <remarks> /// add com "Microsoft Excel 11.0 Object Library" /// using Excel=Microsoft.Office.Interop.Excel; /// using System.Reflection; /// </remarks> /// <param name="dgv" /> DataGridView public static void DataGridViewToExcel(DataGridView dgv) { #region 验证可操作性 //申明保存对话框 SaveFileDialog dlg = new SaveFileDialog(); //默然文件后缀 dlg.DefaultExt = "xls "; //文件后缀列表 dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; //默然路径是系统当前路径 dlg.InitialDirectory = Directory.GetCurrentDirectory(); //打开保存对话框 if (dlg.ShowDialog() == DialogResult.Cancel) return; //返回文件路径 string fileNameString = dlg.FileName; //验证strFileName是否为空或值无效 if (fileNameString.Trim() == " ") { return; } //定义表格内数据的行数和列数 int rowscount = dgv.Rows.Count; int colscount = dgv.Columns.Count; //行数必须大于0 if (rowscount <= 0) { MessageBox.Show("没有数据可供保存!"); return; } //列数必须大于0 if (colscount <= 0) { MessageBox.Show("没有数据可供保存!"); return; } //行数不可以大于65536 if (rowscount > 65536) { MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 "); return; } //列数不可以大于255 if (colscount > 255) { MessageBox.Show("数据记录行数太多，不能保存 "); return; } //验证以fileNameString命名的文件是否存在，如果存在删除它 FileInfo file = new FileInfo(fileNameString); if (file.Exists) { try { file.Delete(); } catch (Exception error) { MessageBox.Show(error.Message); return; } } #endregion Excel.Application objExcel = null; Excel.Workbook objWorkbook = null; Excel.Worksheet objsheet = null; try { //申明对象 objExcel = new Microsoft.Office.Interop.Excel.Application(); objWorkbook = objExcel.Workbooks.Add(Missing.Value); objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet; //设置EXCEL不可见 objExcel.Visible = false; //向Excel中写入表格的表头 int displayColumnsCount = 1; for (int i = 0; i <= dgv.ColumnCount - 1; i ) { if (dgv.Columns[i].Visible == true) { objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim(); displayColumnsCount ; } } //设置进度条 //tempProgressBar.Refresh(); //tempProgressBar.Visible = true; //tempProgressBar.Minimum=1; //tempProgressBar.Maximum=dgv.RowCount; //tempProgressBar.Step=1; //向Excel中逐行逐列写入表格中的数据 for (int row = 0; row <= dgv.RowCount - 1; row ) { //tempProgressBar.PerformStep(); displayColumnsCount = 1; for (int col = 0; col < colscount; col ) { if (dgv.Columns[col].Visible == true) { try { objExcel.Cells[row2,displayColumnsCount]=dgv.Rows[row].Cells[col].Value.ToString(); displayColumnsCount ; } catch (Exception) { } } } } //隐藏进度条 //tempProgressBar.Visible = false; //保存文件 objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception error) { MessageBox.Show(error.Message); return; } finally { //关闭Excel应用 if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value); if (objExcel.Workbooks != null) objExcel.Workbooks.Close(); if (objExcel != null) objExcel.Quit(); objsheet = null; objWorkbook = null; objExcel = null; } MessageBox.Show(fileNameString "\n\n导出完毕! "); }

 

4.用模版的方式导出

/// <summary> /// 导出到Excel /// </summary> /// <param name="sender" /> /// <param name="e" /> private void 导出到ExcelToolStripMenuItem_Click(object sender, EventArgs e) { string excelPath = null; #region 获取Excel保存路径 //using (FolderBrowserDialog myDialog = new FolderBrowserDialog()) //{ // myDialog.ShowNewFolderButton = false; // myDialog.Description = "请选择要保存文件夹或目录"; // if (myDialog.ShowDialog() == DialogResult.OK) // { // excelPath = myDialog.SelectedPath + "\\股东清册.xls"; // } //} #endregion #region 选择Excel模板 using (OpenFileDialog openFile = new OpenFileDialog()) { openFile.Title = "请选择Excel文件模板"; openFile.Filter = "Excel 97-2003 文件模板|*.xls|Excel 2007-2010 文件模板|*.xlsx"; openFile.CheckFileExists = true; openFile.CheckPathExists = true; if (openFile.ShowDialog(this) == DialogResult.OK) { excelPath = openFile.FileName; //MessageBox.Show(openFile.FileName, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("未选择Excel文件模板！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } #endregion System.Data.DataTable dt = new System.Data.DataTable(); DataColumn column; DataRow row; #region //column = new DataColumn(); //column.ColumnName = "序号"; //column.DataType = typeof(string); //dt.Columns.Add(column); //DataColumn column; // row = dt.NewRow(); // row["序号"] = i + 1; // row["股权托管证编号"] = dtStock.Rows[i]["trustee_id"].ToString(); // row["股东帐号"] = dtStock.Rows[i]["stock_account"].ToString(); // row["股东名称"] = dtStock.Rows[i]["customer_name"].ToString(); // row["股权类别"] = dtStock.Rows[i]["stock_bonus_type_name"].ToString(); // row["股权数量"] = dtStock.Rows[i]["begin_amount"].ToString(); // row["联系地址"] = dtStock.Rows[i]["address"].ToString(); // row["邮编"] = dtStock.Rows[i]["zip"].ToString(); // row["电话"] = dtStock.Rows[i]["phone"].ToString(); // dt.Rows.Add(row); #endregion for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1.Columns[j].Visible == true) { column = new DataColumn(); column.ColumnName = dataGridView1.Columns[j].HeaderText; column.DataType = typeof(string); dt.Columns.Add(column); } } for (int i = 0; i < dataGridView1.RowCount; i++) { row = dt.NewRow(); for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1.Columns[j].Visible == true) { row[dataGridView1.Columns[j].HeaderText] = dataGridView1.Rows[i].Cells[j].Value; } } dt.Rows.Add(row); } DataTableToExcel(dt, excelPath); } /// <summary> /// 将Datatable导出Excel时新建Excel /// </summary> /// <param name="dt" /> /// <param name="excelPath" /> /// <returns></returns> public static bool DataTableTonewExcel(System.Data.DataTable dt, string excelPath) { try { object missing = System.Reflection.Missing.Value; Excel.Application app = new Excel.ApplicationClass(); app.Application.Workbooks.Add(true); Excel.Workbook newExcel = (Excel.Workbook)app.ActiveWorkbook; Excel.Worksheet newSheet = (Excel.Worksheet)newExcel.ActiveSheet; int row = dt.Rows.Count; int column = dt.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < column; j++) { newSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } } for (int i = 0; i < column; i++)//设置Sheet的列名 { newSheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName; } newExcel.Save(); app.Cells.Font.Name = "宋体"; //字体 app.Cells.Font.Size = 9; //字号 //保存excel文件 newExcel.SaveCopyAs(excelPath); //关闭文件 newExcel.Close(false, missing, missing); //退出excel app.Quit(); return true; } catch { return false; } finally { KillExcel(); } } /// <summary> /// 将DataTable导出到指定路径Excel模板 /// </summary> /// <param name="dt" /> /// <param name="excelPath" /> /// <returns></returns> public static bool DataTableToExcel(System.Data.DataTable dt, string excelPath) { try { Excel.Application app = new Excel.Application(); app.Visible = true; Excel.Workbook myExcel = app.Workbooks.Open(excelPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Excel.Worksheet mySheet = (Excel.Worksheet)myExcel.Worksheets[1]; int row = dt.Rows.Count; int column = dt.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < column; j++) { mySheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } } for (int i = 0; i < column; i++)//设置Sheet的列名 { mySheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName; } myExcel.Save(); app.Cells.Font.Name = "宋体"; //字体 app.Cells.Font.Size = 9; //字号 app.DisplayAlerts = false; //覆盖时不提示 app.AlertBeforeOverwriting = false; app.SaveWorkspace(excelPath); app.Quit(); app = null; return true; } catch { return false; } finally { KillExcel(); } } /// <summary> /// 关闭Excel进程 /// </summary> private static void KillExcel() { System.Diagnostics.Process[] pro = System.Diagnostics.Process.GetProcessesByName("Excel"); foreach (System.Diagnostics.Process killpro in pro) { killpro.Kill(); } }

本文中有部分内容转载自:http://jclhy.bokee.com/viewdiary.41216948.html