using System;
using
System.Collections.Generic;
using
System.Text;
using
System.Data;
using
System.Windows.Forms;
using
System.Reflection;
namespace
DMS
{
///
<summary>
/// C#操作Excel类
///
</summary>
class
ExcelOperate
{
//法一
//public
bool DataSetToExcel(DataSet dataSet, bool
isShowExcle)
//{
//
DataTable dataTable =
dataSet.Tables[0];
// int rowNumber =
dataTable.Rows.Count;
// int
columnNumber =
dataTable.Columns.Count;
// if
(rowNumber == 0)
//
{
//
MessageBox.Show("没有任何数据可以导入到Excel文件!");
//
return false;
//
}
//
//建立Excel对象
//
Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//
excel.Application.Workbooks.Add(true);
//
excel.Visible =
isShowExcle;//是否打开该Excel文件
//
//填充数据
// for (int c = 0; c <
rowNumber; c++)
//
{
// for (int j
= 0; j < columnNumber;
j++)
//
{
//
excel.Cells[c + 1, j + 1] =
dataTable.Rows[c].ItemArray[j];
//
}
//
}
// return
true;
//}
//法二
//public
bool DataSetToExcel(DataSet dataSet, bool
isShowExcle)
//{
//
DataTable dataTable =
dataSet.Tables[0];
// int rowNumber =
dataTable.Rows.Count;
// int
rowIndex = 1;
// int colIndex =
0;
// if (rowNumber ==
0)
//
{
// return
false;
//
}
//
//建立Excel对象
//
Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//
excel.Application.Workbooks.Add(true);
//
excel.Visible = isShowExcle;
//
//生成字段名称
// foreach (DataColumn col in
dataTable.Columns)
//
{
//
colIndex++;
//
excel.Cells[1, colIndex] =
col.ColumnName;
//
}
//
//填充数据
// foreach (DataRow row in
dataTable.Rows)
//
{
//
rowIndex++;
//
colIndex = 0;
//
foreach (DataColumn col in
dataTable.Columns)
//
{
//
colIndex++;
//
excel.Cells[rowIndex, colIndex] =
row[col.ColumnName];
//
}
//
}
// return
true;
//}
//法三(速度最快)
///
<summary>
///
将数据集中的数据导出到EXCEL文件
///
</summary>
/// <param
name="dataSet">输入数据集</param>
/// <param
name="isShowExcle">是否显示该EXCEL文件</param>
///
<returns></returns>
public bool
DataSetToExcel(DataSet dataSet, bool
isShowExcle)
{
DataTable dataTable =
dataSet.Tables[0];
int rowNumber =
dataTable.Rows.Count;//不包括字段名
int columnNumber =
dataTable.Columns.Count;
int colIndex =
0;
if (rowNumber ==
0)
{
return
false;
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application
excel = new
Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook
workbook =
excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet
worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible
= isShowExcle;
//Microsoft.Office.Interop.Excel.Worksheet
worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range
range;
//生成字段名称
foreach
(DataColumn col in
dataTable.Columns)
{
colIndex++;
excel.Cells[1,
colIndex] =
col.ColumnName;
}
object[,]
objData = new object[rowNumber, columnNumber];
for
(int r = 0; r < rowNumber;
r++)
{
for (int c = 0; c <
columnNumber; c++)
{
objData[r, c] =
dataTable.Rows[r][c];
}
//Application.DoEvents();
}
//
写入Excel
range = worksheet.get_Range(excel.Cells[2, 1],
excel.Cells[rowNumber + 1,
columnNumber]);
//range.NumberFormat =
"@";//设置单元格为文本格式
range.Value2 =
objData;
worksheet.get_Range(excel.Cells[2, 1],
excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d
h:mm";
return
true;
}
//法四
//public
bool DataSetToExcel(DataSet dataSet, bool
isShowExcle)
//{
//
DataTable dataTable =
dataSet.Tables[0];
// int rowNumber =
dataTable.Rows.Count;
// int
columnNumber =
dataTable.Columns.Count;
// String
stringBuffer = "";
// if (rowNumber
== 0)
//
{
//
MessageBox.Show("没有任何数据可以导入到Excel文件!");
//
return false;
//
}
//
//建立Excel对象
//
Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//
excel.Application.Workbooks.Add(true);
//
excel.Visible =
isShowExcle;//是否打开该Excel文件
//
//填充数据
// for (int i = 0; i <
rowNumber; i++)
//
{
// for (int j
= 0; j < columnNumber;
j++)
//
{
//
stringBuffer +=
dataTable.Rows[i].ItemArray[j].ToString();
//
if (j < columnNumber -
1)
//
{
//
stringBuffer +=
"\t";
//
}
//
}
//
stringBuffer += "\n";
//
}
//
Clipboard.Clear();
//
Clipboard.SetDataObject(stringBuffer);
//
((Microsoft.Office.Interop.Excel.Range)excel.Cells[1,
1]).Select();
//
((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet).Paste(Missing.Value,
Missing.Value);
//
Clipboard.Clear();
// return
true;
//}
//public bool
DataSetToExcel(DataSet dataSet, string fileName, bool
isShowExcle)
//{
//
DataTable dataTable =
dataSet.Tables[0];
// int rowNumber =
dataTable.Rows.Count;
// int
columnNumber =
dataTable.Columns.Count;
// if
(rowNumber == 0)
//
{
//
MessageBox.Show("没有任何数据可以导入到Excel文件!");
//
return false;
//
}
//
//建立Excel对象
//
Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//
Microsoft.Office.Interop.Excel.Workbook workBook =
excel.Application.Workbooks.Add(true);
//
excel.Visible =
false;//是否打开该Excel文件
//
//填充数据
// for (int i = 0; i <
rowNumber; i++)
//
{
// for (int j
= 0; j < columnNumber;
j++)
//
{
//
excel.Cells[i + 1, j + 1] =
dataTable.Rows[i].ItemArray[j];
//
}
//
}
// //string fileName = path + "\\"
+ DateTime.Now.ToString().Replace(‘:‘, ‘_‘) +
".xls";
// workBook.SaveAs(fileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
//
try
//
{
//
workBook.Saved =
true;
//
excel.UserControl =
false;
//
//excelapp.Quit();
//
}
// catch (Exception
exception)
//
{
//
MessageBox.Show(exception.Message);
//
}
//
finally
//
{
//
workBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges,
Missing.Value,
Missing.Value);
//
excel.Quit();
//
}
// if
(isShowExcle)
//
{
//
System.Diagnostics.Process.Start(fileName);
//
}
// return
true;
//}
//public bool
DataSetToExcel(DataSet dataSet, string fileName, bool
isShowExcle)
//{
//
DataTable dataTable =
dataSet.Tables[0];
// int rowNumber =
dataTable.Rows.Count;//不包括字段名
// int
columnNumber =
dataTable.Columns.Count;
// int colIndex
= 0;
// if (rowNumber ==
0)
//
{
//
MessageBox.Show("没有任何数据可以导入到Excel文件!");
//
return false;
//
}
//
//建立Excel对象
//
Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//
//excel.Application.Workbooks.Add(true);
//
Microsoft.Office.Interop.Excel.Workbook workbook =
excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//
excel.Visible = isShowExcle;
//
//Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
//
worksheet.Name = "挠度数据";
//
Microsoft.Office.Interop.Excel.Range
range;
//
//生成字段名称
// foreach (DataColumn col in
dataTable.Columns)
//
{
//
colIndex++;
//
excel.Cells[1, colIndex] =
col.ColumnName;
//
}
// object[,] objData = new
object[rowNumber, columnNumber];
//
for (int r = 0; r < rowNumber;
r++)
//
{
// for (int c
= 0; c < columnNumber;
c++)
//
{
//
objData[r, c] =
dataTable.Rows[r][c];
//
}
//
//Application.DoEvents();
//
}
// //
写入Excel
// range =
worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1,
columnNumber]);
// //range.NumberFormat
= "@";//设置单元格为文本格式
// range.Value2 =
objData;
//
worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1,
1]).NumberFormat = "yyyy-m-d
h:mm";
// //string fileName = path +
"\\" + DateTime.Now.ToString().Replace(‘:‘, ‘_‘) +
".xls";
// workbook.SaveAs(fileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
//
try
//
{
//
workbook.Saved =
true;
//
excel.UserControl =
false;
//
//excelapp.Quit();
//
}
// catch (Exception
exception)
//
{
//
MessageBox.Show(exception.Message);
//
}
//
finally
//
{
//
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges,
Missing.Value,
Missing.Value);
//
excel.Quit();
//
}
// //if
(isShowExcle)
//
//{
// //
System.Diagnostics.Process.Start(fileName);
//
//}
// return
true;
//}
///
<summary>
///
将数据集中的数据保存到EXCEL文件
///
</summary>
/// <param
name="dataSet">输入数据集</param>
/// <param
name="fileName">保存EXCEL文件的绝对路径名</param>
///
<param
name="isShowExcle">是否打开EXCEL文件</param>
///
<returns></returns>
public bool
DataSetToExcel(DataSet dataSet, string fileName, bool
isShowExcle)
{
DataTable dataTable =
dataSet.Tables[0];
int rowNumber =
dataTable.Rows.Count;//不包括字段名
int columnNumber =
dataTable.Columns.Count;
int colIndex =
0;
if (rowNumber ==
0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件!");
return
false;
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application
excel = new
Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook
workbook =
excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet
worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible
= false;
//Microsoft.Office.Interop.Excel.Worksheet
worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range
range;
//生成字段名称
foreach
(DataColumn col in
dataTable.Columns)
{
colIndex++;
excel.Cells[1,
colIndex] =
col.ColumnName;
}
object[,]
objData = new object[rowNumber, columnNumber];
for
(int r = 0; r < rowNumber;
r++)
{
for (int c = 0; c <
columnNumber; c++)
{
objData[r, c] =
dataTable.Rows[r][c];
}
//Application.DoEvents();
}
//
写入Excel
range = worksheet.get_Range(excel.Cells[2, 1],
excel.Cells[rowNumber + 1,
columnNumber]);
//range.NumberFormat =
"@";//设置单元格为文本格式
range.Value2 =
objData;
worksheet.get_Range(excel.Cells[2, 1],
excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d
h:mm";
//string fileName = path + "\\" +
DateTime.Now.ToString().Replace(‘:‘, ‘_‘) +
".xls";
workbook.SaveAs(fileName, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
try
{
workbook.Saved
= true;
excel.UserControl =
false;
//excelapp.Quit();
}
catch
(Exception
exception)
{
MessageBox.Show(exception.Message);
}
finally
{
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges,
Missing.Value,
Missing.Value);
excel.Quit();
}
if
(isShowExcle)
{
System.Diagnostics.Process.Start(fileName);
}
return
true;
}
}
}
相关文章
- 11-02如何将数据库中的数据导成 excel 文件
- 11-02c#中数据从数据库到客户端主要几种的导出方式(导出到excel,导出到word)
- 11-02C#将数据集DataSet中的数据导出到EXCEL文件的几种方法
- 11-02效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
- 11-02将SQL SERVER中查询到的数据导成一个Excel文件
- 11-02C# 将DataGridView中显示的数据导出到Excel(.xls和.xlsx格式)—NPOI
- 11-02mysql导入导出数据中文乱码解决方法小结(1、navicat导入问题已解决,创建连接后修改连接属性,选择高级->将使用Mysql字符集复选框去掉,下拉框选择GBK->导入sql文件OK;2、phpmyadmin显示乱码的问题也解决,两步:1.将sql文件以utf8的字符集编码另存,2.将文件中sql语句中的字段字符集编码改成utf8,导入OK)
- 11-02C#将数据集DataSet中的数据导出到EXCEL文件的几种方法
- 11-02C# 将内存中的datatable数据导出为Excel(方法二,创建Excel对象导出)【转载】
- 11-02C# 将内存中的datatable数据导出为Excel(方法一,以文件流方式导出)【转载】