using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data ;
using System.Data.OleDb;
namespace ExportExcel
{
public class ExportExcel
{
public static void ExportToExcel(DataGridView dgv, string reportTitle)
{
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
int rowIndex = 2;
int colIndex = 0;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, dgv.ColumnCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = reportTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
foreach (DataGridViewColumn column in dgv.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = column.HeaderText;
}
for (int row = 0; row < dgv.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
for (int col = 0; col < dgv.Columns.Count; col++)
{
xlApp.Cells[rowIndex, col + 1] = dgv.Rows[row].Cells[col].Value.ToString();
}
}
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, dgv.Columns.Count]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 0;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
try
{
xlApp.Visible = false;
xlApp.Save(reportTitle);
}
catch
{
}
finally
{
xlApp.Quit();
}
}
public static void ExportToExcel(DataGridView dgv)
{
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
int rowIndex = 1;
int colIndex = 0;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
foreach (DataGridViewColumn column in dgv.Columns)
{
colIndex++;
xlApp.Cells[1, colIndex] = column.HeaderText;
}
for (int row = 0; row < dgv.Rows.Count; row++)
{
rowIndex++;
for (int col = 0; col < dgv.Columns.Count; col++)
{
xlApp.Cells[rowIndex, col + 1] = dgv.Rows[row].Cells[col].Value.ToString();
}
}
xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, dgv.Columns.Count]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 0;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
try
{
xlApp.Visible = false;
xlApp.Save("Sheet1");
}
catch
{
}
finally
{
xlApp.Quit();
}
}
public static void ExportToExcel(DataSet ds, string fileStrName)
{
Excel.Application excel = new Excel.ApplicationClass();
if (excel == null)
{
MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
int rowindex = 2;
int colindex = 0;
excel.Application.Workbooks.Add(true);
DataTable dt = ds.Tables[0];
Excel.Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns .Count]);
range.MergeCells = true;
excel.ActiveCell.FormulaR1C1 =fileStrName;
excel.ActiveCell.Font.Size = 18;
excel.ActiveCell.Font.Bold = true;
foreach (DataColumn col in dt.Columns)
{
colindex=colindex +1;
excel.Cells[2, colindex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
colindex = 0;
rowindex++;
foreach (DataColumn col in dt.Columns)
{
colindex++;
excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
}
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[rowindex, colindex]).Borders.LineStyle = 0;
excel.Cells.EntireColumn.AutoFit();
excel.Cells.VerticalAlignment = Excel.Constants.xlCenter;
excel.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
try
{
excel.Visible = false;
excel.Save("Sheet1");
}
catch { }
finally
{
excel.Quit();
excel = null;
}
}
public static void ExportToExcel(DataSet ds)
{
Excel.Application excel = new Excel.ApplicationClass();
if (excel == null)
{
MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
int rowindex = 1;
int colindex = 0;
excel.Application.Workbooks.Add(true);
DataTable dt = ds.Tables[0];
foreach (DataColumn col in dt.Columns)
{
colindex++;
excel.Cells[1, colindex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
colindex = 0;
rowindex++;
foreach (DataColumn col in dt.Columns)
{
colindex++;
excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
}
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[rowindex, colindex]).Borders.LineStyle = 0;
excel.Cells.EntireColumn.AutoFit();
excel.Cells.VerticalAlignment = Excel.Constants.xlCenter;
excel.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
try
{
excel.Visible = false;
excel.Save("Sheet1");
}
catch { }
finally
{
excel.Quit();
excel = null;
}
}
public static DataSet ExcelToDataSet(string strFilePath)
{
DataSet ds = null;
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFilePath + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from [Sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
}
catch
{
return null;
MessageBox.Show("Excel文件属性设置有误,不能导入!");
}
return ds;
}
public static void ExportToText(DataGridView dgv, string reportTitle)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Title = "输出报表";
dlg.Filter = "文本文件(*.txt)|*.txt";
if (DialogResult.OK == dlg.ShowDialog())
{
//遍历求出各列内容的最大长度，以便按格式对齐
int[] colContentLength = new int[dgv.ColumnCount];
for (int row = 0; row < dgv.Rows.Count; row++)
{
for (int col = 0; col < dgv.ColumnCount; col++)
{
if (dgv.Rows[row].Cells[col].Value.ToString().Length > colContentLength[col])
{
colContentLength[col] = dgv.Rows[row].Cells[col].Value.ToString().Length;
}
}
}
string fileName = dlg.FileName;
FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs);
//通过流来写文件
try
{
sw.WriteLine(reportTitle);
sw.WriteLine();
sw.WriteLine("----------------------------------------------------------");
//写列名
int position = 0;
foreach (DataGridViewColumn column in dgv.Columns)
{
sw.Write(column.HeaderText.PadRight(colContentLength[position++] + 4));
}
//写内容
for (int row = 0; row < dgv.Rows.Count; row++)
{
sw.WriteLine();
for (int col = 0; col < dgv.ColumnCount; col++)
{
sw.Write(dgv.Rows[row].Cells[col].Value.ToString().PadRight(colContentLength[col] +
);
}
}
sw.WriteLine();
sw.WriteLine("----------------------------------------------------------");
sw.Flush();
}
catch
分享到：
相关推荐
C#DataSet导出EXCEL的方法 代码分析 可以参考，个人搜索
解决.net DataSet导出excel问题
DataSet 导出Excel, 需要就下吧！
dataset导出excel,多表导出，有shell标签，可设置表头
.net（dataset）输出流导出excel（无需生成模版excel文件，直接输出数据流导出excel表格）
DataSet 或 DataTable 导出到 Excel
DataSet导出到Office 2007 Excel 支持服务器端杀死excel.exe进程
方便好用的类模板，传入dataset 就可以生成EXCEL。 要添加引用Microsoft.Office.Interop.Excel.dll
导入导出EXCEL的源代码.[导出带线的Excel,导入DataSet] 只需要将该包Comm添加到项目,调用其对应的导入\导出类中的方法使用.[导入导出源代码]
将Dataset导出到Excel，本程序阐述基本功能-Dataset exported to Excel, the program described the basic functions of
asp.net里导出excel的方法汇总，如有不对请指正！
C# 源码 EXCEL导入到Dataset，Dataset导出到Excel
功能描述： 1、选择当前路径下的所有xls文件（xls文件必须是统一格式）；...3、从Dataset导出到Excel表格 注：此代码运行是需有office组建作为支持，即运行该程序的电脑上必须安装office软件；程序由VS2012开发。
NPOI导出excel表格，dataset导出excel表格，用了NPOI类库后直接在项目中把这段代码粘贴，引用NPOI.dll就可以了~~~
ASP.NET用DataSet导出到Excel的方法，需要的朋友可以参考一下
delphi 轻松导出数据到excel，只需引用一个单元，然后用一个方法
Delphi DataSet导出Excel文件最快速的代码，不用外挂，直接嵌合到程序中。
C#导出DataSet到EXCEL,CSV
C#从dataset数据集导出EXCEL功能代码包括复杂表头，合并单元格等，可以任意设置，修改