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] + 8));
}
}
sw.WriteLine();
sw.WriteLine("----------------------------------------------------------");
sw.Flush();
}
catch
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] + 8));
}
}
sw.WriteLine();
sw.WriteLine("----------------------------------------------------------");
sw.Flush();
}
catch