- #region 导出当前页到Excel中
-
-
- private void button7_Click(object sender, EventArgs e)
- {
- print(dataGridView1);
- }
- public void print(DataGridView dataGridView1)
- {
-
- try
- {
-
- if (dataGridView1.Rows.Count == 0)
- return;
-
- Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
-
-
- excel.Visible = false;
-
-
- excel.Application.Workbooks.Add(true);
-
- for (int i = 0; i < dataGridView1.Columns.Count; i++)
- {
- excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
- }
-
- for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
- {
- for (int j = 0; j < dataGridView1.Columns.Count; j++)
- {
- if (dataGridView1[j, i].ValueType == typeof(string))
- {
- excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
- }
- else
- {
- excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
- }
- }
- }
-
-
- excel.DisplayAlerts = false;
- excel.AlertBeforeOverwriting = false;
-
-
- excel.Application.Workbooks.Add(true).Save();
-
- excel.Save("D:" + "//KKHMD.xls");
-
-
- excel.Quit();
- excel = null;
-
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message, "错误提示");
- }
- }
- #endregion
#region 导出当前页到Excel中
//按下导出按钮
private void button7_Click(object sender, EventArgs e)
{
print(dataGridView1);
}
public void print(DataGridView dataGridView1)
{
//导出到execl
try
{
//没有数据的话就不往下执行
if (dataGridView1.Rows.Count == 0)
return;
//实例化一个Excel.Application对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写
excel.Visible = false;
//新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错
excel.Application.Workbooks.Add(true);
//生成Excel中列头名称
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//把DataGridView当前页的数据保存在Excel中
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//保存工作簿
excel.Application.Workbooks.Add(true).Save();
//保存excel文件
excel.Save("D:" + "//KKHMD.xls");
//确保Excel进程关闭
excel.Quit();
excel = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}
#endregion
导出从数据库中查询到的所有的数据到Excel中
- #region 导出全部数据到Excel中,可弹出保存对话框,但没用SaveFileDialog
-
- public void printAll(System.Data.DataTable dt)
- {
-
- try
- {
-
- if (dt.Rows.Count == 0)
- return;
-
- Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
-
-
- excel.Application.Workbooks.Add(true);
-
-
- excel.Visible = false;
-
- for (int i = 0; i < dt.Columns.Count; i++)
- {
- excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
- }
-
-
- if (dt.Rows.Count > 0)
- {
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- for (int j = 0; j < dt.Columns.Count; j++)
- {
- string str = dt.Rows[i][j].ToString();
- excel.Cells[i + 2, j + 1] = "'" + str;
- }
- }
- }
-
- excel.DisplayAlerts = false;
- excel.AlertBeforeOverwriting = false;
-
-
- excel.Application.Workbooks.Add(true).Save();
-
- excel.Save("D:" + "//KKHMD.xls");
-
-
- excel.Quit();
- excel = null;
-
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message, "错误提示");
- }
- }
- private void button1_Click(object sender, EventArgs e)
- {
- printAll(dt);
- }
- #endregion
- private void menuItem16_Click(object sender, System.EventArgs e)
{ ds.Tables["CurrentTable"].AcceptChanges(); this.Cursor=Cursors.WaitCursor; Excel.Application myExcel=new Excel.Application(); myExcel.Application.Workbooks.Add(true); myExcel.Visible=true; myExcel.Cells[1,1]=this.dataGrid1.CaptionText; //Object Date[ds.Tables["CurrentTable"].Rows.Count][ds.Tables["CurrentTable"].Columns.Count]; //生成标题 for(int i=0;i<ds.Tables["CurrentTable"].Columns.Count;i++) { myExcel.Cells[2,i+1]=ds.Tables["CurrentTable"].Columns[i].Caption; } //填充数据 for(int i=0;i<ds.Tables["CurrentTable"].Rows.Count;i++) { for(int j=0;j<ds.Tables["CurrentTable"].Columns.Count;j++) { if(ds.Tables["CurrentTable"].Rows[i][j].GetType()==typeof(System.String)) - {
- myExcel.Cells[i+3,j+1]="'"+ds.Tables["CurrentTable"].Rows[i][j].ToString();
} else { myExcel.Cells[i+3,j+1]=ds.Tables["CurrentTable"].Rows[i][j].ToString(); } } } - this.Cursor=Cursors.Default;
MessageBox.Show("导出成功! ","恭喜",MessageBoxButtons.OK,MessageBoxIcon.Asterisk); } - @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
- private void button5_Click_1(object sender, EventArgs e)
{ //建立Excel对象 Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); //生成字段名称 for (int i = 0; i < dataGridView2.ColumnCount; i++) { excel.Cells[1,i + 1] = dataGridView2.Columns[i].HeaderText; if (y == 0) { y = 1; toolStripStatusLabel6.Text = "数据导入中,请等待!"; } } //填充数据 for (int i = 0; i < dataGridView2.RowCount - 1; i++) { for (int j = 0; j < dataGridView2.ColumnCount; j++) { if (dataGridView2[j, i].Value == typeof(string)) { excel.Cells[i + 2, j + 1] = "" + dataGridView2[i, j].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dataGridView2[j, i].Value.ToString(); } } } excel.Visible = true; }
{ /// <summary>
- /// </summary>
/// <param name="mydgv">控件 DataGridView </param> /// <param name="dic">中英文对照的标题 </param> public static void ExportTasks(DataGridView mydgv, Dictionary <string, string> dic) { // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; //定义Range对象,此对象代表单元格区域 Microsoft.Office.Interop.Excel.Range range; - int dcell = 1;
int rowindex = 0; int colindex = 0; - int rowcount = mydgv.Rows.Count;
int colcount = mydgv.Columns.Count; int dispcolcount = dic.Count; try { //初始化 Application 对象 excelApp excelApp = new Microsoft.Office.Interop.Excel.Application(); //在工作薄的第一个工作表上创建任务列表 workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); ws = (Worksheet)workBook.Worksheets[1]; - // 命名工作表的名称为
ws.Name = "Sheet1"; //创建缓存 Object[,] objdata = new object[rowcount + 1, colcount]; //创建标题 foreach (string s in dic.Keys) { objdata[rowindex, colindex++] = dic[s].ToString(); } //获取数据 for (int i = 0; i < rowcount; i++) { dcell = 0; foreach (string ss in dic.Keys) { for (int j = 0; j < colcount; j++) { if (mydgv.Columns[j].Name == ss) { objdata[i + 1, dcell++] = mydgv.Rows[i].Cells[j].FormattedValue.ToString(); //得到样式之后的值 } } } } //写入Excel range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount, dispcolcount]); range.Value2 = objdata; System.Windows.Forms.Application.DoEvents(); //设置格式 excelApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; //全局左对齐 excelApp.Cells.EntireColumn.AutoFit(); range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[1, colcount]); range.Font.Bold = true; //标题粗体 //显示 Excel excelApp.Visible = true; - }
catch (Exception ex) { throw ex; } ########################################################################### - using System;
using System.Collections.Generic; using System.Text; using System.Web.UI.WebControls; using System.Data; using System.Web.UI.HtmlControls; namespace Common { public class ExcelHelper { // Excel导出 public static void Export(GridView dgExport, DataTable dtData) { System.Web.HttpContext curContext = System.Web.HttpContext.Current; // IO用于导出并返回excel文件 System.IO.StringWriter strWriter = null; System.Web.UI.HtmlTextWriter htmlWriter = null; - if (dtData != null)
{ // 设置编码和附件格式 curContext.Response.Clear(); curContext.Response.ClearContent(); curContext.Response.Buffer = true; curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-7"); curContext.Response.Charset = "GB2312"; curContext.Response.AppendHeader("content-disposition", "filename=/"" + System.Web.HttpUtility.UrlEncode(dtData.TableName, System.Text.Encoding.UTF8) + ".xls/""); // 导出excel文件 strWriter = new System.IO.StringWriter(); htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter); - HtmlForm frm = new HtmlForm();
frm.Attributes["runat"] = "server"; frm.Controls.Add(dgExport); dgExport.DataSource = dtData.DefaultView; dgExport.DataBind(); // 返回客户端 dgExport.RenderControl(htmlWriter); curContext.Response.Write(strWriter.ToString()); curContext.Response.End(); } } } }
|