c#操作Excel和把Dataset数据写入Excel
来源:互联网 发布:omp算法matlab代码 编辑:程序博客网 时间:2024/05/22 09:07
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
namespace WebOp.Common
{
class ExcelOperation
{
#region OPEN WORKBOOK VARIABLES
private static object vk_missing = System.Reflection.Missing.Value;
private static object vk_visible = true;
private static object vk_false = false;
private static object vk_true = true;
private static object vk_update_links = 0;
private static object vk_read_only = vk_true;
private static object vk_format = 1;
private static object vk_password = vk_missing;
private static object vk_write_res_password = vk_missing;
private static object vk_ignore_read_only_recommend = vk_true;
private static object vk_origin = vk_missing;
private static object vk_delimiter = vk_missing;
private static object vk_editable = vk_false;
private static object vk_notify = vk_false;
private static object vk_converter = vk_missing;
private static object vk_add_to_mru = vk_false;
private static object vk_local = vk_false;
private static object vk_corrupt_load = vk_false;
#endregion
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="excelSheet">excel的worksheet</param>
/// <param name="row">行号</param>
/// <param name="col">列号</param>
/// <returns>单元格内值</returns>
public static string getCellValue(Worksheet excelSheet, int row, int col)
{
string value = string.Empty;
if (col != 0)
{
value = excelSheet.get_Range(excelSheet.Cells[row, col], excelSheet.Cells[row, col]).Text.ToString().Trim();
}
if (value.ToUpper().Equals("NULL") || value.Equals("无") || value.Equals("暂无"))
{
value = string.Empty;
}
return value;
}
/// <summary>
/// 关闭excel进程
/// </summary>
/// <param name="excelAp">需要关闭的excel进程</param>
public static void closeExcel(Microsoft.Office.Interop.Excel.Application excelAp)
{
if (excelAp != null)
{
excelAp.DisplayAlerts = false;
excelAp.Workbooks.Close();
excelAp.Quit();
}
}
/// <summary>
/// 开始一个excel进程
/// </summary>
/// <returns>启动的excel进程</returns>
public static Microsoft.Office.Interop.Excel.Application getExcelApplication()
{
Microsoft.Office.Interop.Excel.Application excelAp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelAp.Visible = false;
excelAp.DisplayAlerts = true;
excelAp.UserControl = true;
return excelAp;
}
/// <summary>
/// 获取一个excel的第一个worksheet页
/// </summary>
/// <param name="excelAp">excel进程</param>
/// <param name="excelLocation">打开的excel的文件位置</param>
/// <param name="excelBook">返回的打开的excel文件</param>
/// <returns>excel文件的第一页</returns>
public static Worksheet getWorksheet(Microsoft.Office.Interop.Excel.Application excelAp, string excelLocation, out Workbook excelBook)
{
excelBook = null;
if (!File.Exists(excelLocation))
{
return null;
}
try
{
Worksheet excelSheet = null;
excelBook = excelAp.Workbooks.Open(excelLocation, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
foreach (Worksheet displayWorksheet in excelBook.Sheets)
{
excelSheet = displayWorksheet;
break;
}
if (excelSheet == null)
{
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[excelBook.Sheets.Count] as Worksheet;
}
return excelSheet;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 获取一个excel的指定的worksheet页,如果不存在此页名,则用此名创建一个新页
/// </summary>
/// <param name="excelAp">excel进程</param>
/// <param name="excelLocation">打开的excel的文件位置</param>
/// <param name="sheetName">页名</param>
/// <param name="excelBook">返回的打开的excel文件</param>
/// <returns>excel文件的指定页</returns>
public static Worksheet getWorksheet(Microsoft.Office.Interop.Excel.Application excelAp, string excelLocation, string sheetName, out Workbook excelBook)
{
excelBook = null;
if (!File.Exists(excelLocation))
{
return null;
}
try
{
Worksheet excelSheet = null;
excelBook = excelAp.Workbooks.Open(excelLocation, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
foreach (Worksheet displayWorksheet in excelBook.Sheets)
{
if (displayWorksheet.Name.Equals(sheetName))
{
excelSheet = displayWorksheet;
break;
}
}
if (excelSheet == null)
{
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[excelBook.Sheets.Count] as Worksheet;
excelSheet.Name = sheetName;
}
return excelSheet;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 将dataset中的数据导入到excel中
/// </summary>
/// <param name="ds">存放原始数据的dataset,可从数据库获取</param>
/// <param name="excelFileFullPath">excel路径</param>
/// <param name="sheetName">excel的sheet页名</param>
/// <param name="errormsg">运行过程中产生的错误信息,如果成功则为空</param>
/// <returns>导入成功则为true,否则为false</returns>
public static bool exportExcel(DataSet ds, string excelFileFullPath, string sheetName,out string errormsg)
{
errormsg = string.Empty;
if (ds.Tables.Count == 0 || string.IsNullOrEmpty(excelFileFullPath))
{
errormsg = "没有数据";
return false;
}
bool bexistfile = false;
if (File.Exists(excelFileFullPath))
{
bexistfile = true;
}
Microsoft.Office.Interop.Excel.Application excelAp = getExcelApplication();
try
{
int rowindex = 1, colindex = 0;
Workbook excelBook;
Worksheet excelSheet;
if (bexistfile)
{
excelSheet = getWorksheet(excelAp, excelFileFullPath, sheetName,out excelBook);
}
else
{
excelBook = excelAp.Workbooks.Add(true);
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[1] as Worksheet;
excelSheet.Name = sheetName;
}
System.Data.DataTable ddt = ds.Tables[0];
foreach (DataColumn col in ddt.Columns)
{
colindex++;
excelSheet.Cells[1, colindex] = col.ColumnName;
}
foreach (DataRow row in ddt.Rows)
{
rowindex++;
colindex = 0;
foreach (DataColumn col in ddt.Columns)
{
colindex++;
excelSheet.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
}
}
XlFileFormat xlff = XlFileFormat.xlWorkbookNormal;
if (excelFileFullPath.EndsWith("xls"))
{
xlff = XlFileFormat.xlExcel8;
}
else if (excelFileFullPath.EndsWith("xlsx"))
{
xlff = XlFileFormat.xlExcel12;
}
if (bexistfile)
{
excelBook.Save();
}
else
{
excelBook.SaveAs(excelFileFullPath, xlff, null, null, false, false, XlSaveAsAccessMode.xlNoChange,
null, null, null, null, null);
}
closeExcel(excelAp);
return true;
}
catch (Exception ex)
{
errormsg = ex.Message;
closeExcel(excelAp);
return false;
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
namespace WebOp.Common
{
class ExcelOperation
{
#region OPEN WORKBOOK VARIABLES
private static object vk_missing = System.Reflection.Missing.Value;
private static object vk_visible = true;
private static object vk_false = false;
private static object vk_true = true;
private static object vk_update_links = 0;
private static object vk_read_only = vk_true;
private static object vk_format = 1;
private static object vk_password = vk_missing;
private static object vk_write_res_password = vk_missing;
private static object vk_ignore_read_only_recommend = vk_true;
private static object vk_origin = vk_missing;
private static object vk_delimiter = vk_missing;
private static object vk_editable = vk_false;
private static object vk_notify = vk_false;
private static object vk_converter = vk_missing;
private static object vk_add_to_mru = vk_false;
private static object vk_local = vk_false;
private static object vk_corrupt_load = vk_false;
#endregion
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="excelSheet">excel的worksheet</param>
/// <param name="row">行号</param>
/// <param name="col">列号</param>
/// <returns>单元格内值</returns>
public static string getCellValue(Worksheet excelSheet, int row, int col)
{
string value = string.Empty;
if (col != 0)
{
value = excelSheet.get_Range(excelSheet.Cells[row, col], excelSheet.Cells[row, col]).Text.ToString().Trim();
}
if (value.ToUpper().Equals("NULL") || value.Equals("无") || value.Equals("暂无"))
{
value = string.Empty;
}
return value;
}
/// <summary>
/// 关闭excel进程
/// </summary>
/// <param name="excelAp">需要关闭的excel进程</param>
public static void closeExcel(Microsoft.Office.Interop.Excel.Application excelAp)
{
if (excelAp != null)
{
excelAp.DisplayAlerts = false;
excelAp.Workbooks.Close();
excelAp.Quit();
}
}
/// <summary>
/// 开始一个excel进程
/// </summary>
/// <returns>启动的excel进程</returns>
public static Microsoft.Office.Interop.Excel.Application getExcelApplication()
{
Microsoft.Office.Interop.Excel.Application excelAp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelAp.Visible = false;
excelAp.DisplayAlerts = true;
excelAp.UserControl = true;
return excelAp;
}
/// <summary>
/// 获取一个excel的第一个worksheet页
/// </summary>
/// <param name="excelAp">excel进程</param>
/// <param name="excelLocation">打开的excel的文件位置</param>
/// <param name="excelBook">返回的打开的excel文件</param>
/// <returns>excel文件的第一页</returns>
public static Worksheet getWorksheet(Microsoft.Office.Interop.Excel.Application excelAp, string excelLocation, out Workbook excelBook)
{
excelBook = null;
if (!File.Exists(excelLocation))
{
return null;
}
try
{
Worksheet excelSheet = null;
excelBook = excelAp.Workbooks.Open(excelLocation, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
foreach (Worksheet displayWorksheet in excelBook.Sheets)
{
excelSheet = displayWorksheet;
break;
}
if (excelSheet == null)
{
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[excelBook.Sheets.Count] as Worksheet;
}
return excelSheet;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 获取一个excel的指定的worksheet页,如果不存在此页名,则用此名创建一个新页
/// </summary>
/// <param name="excelAp">excel进程</param>
/// <param name="excelLocation">打开的excel的文件位置</param>
/// <param name="sheetName">页名</param>
/// <param name="excelBook">返回的打开的excel文件</param>
/// <returns>excel文件的指定页</returns>
public static Worksheet getWorksheet(Microsoft.Office.Interop.Excel.Application excelAp, string excelLocation, string sheetName, out Workbook excelBook)
{
excelBook = null;
if (!File.Exists(excelLocation))
{
return null;
}
try
{
Worksheet excelSheet = null;
excelBook = excelAp.Workbooks.Open(excelLocation, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
foreach (Worksheet displayWorksheet in excelBook.Sheets)
{
if (displayWorksheet.Name.Equals(sheetName))
{
excelSheet = displayWorksheet;
break;
}
}
if (excelSheet == null)
{
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[excelBook.Sheets.Count] as Worksheet;
excelSheet.Name = sheetName;
}
return excelSheet;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 将dataset中的数据导入到excel中
/// </summary>
/// <param name="ds">存放原始数据的dataset,可从数据库获取</param>
/// <param name="excelFileFullPath">excel路径</param>
/// <param name="sheetName">excel的sheet页名</param>
/// <param name="errormsg">运行过程中产生的错误信息,如果成功则为空</param>
/// <returns>导入成功则为true,否则为false</returns>
public static bool exportExcel(DataSet ds, string excelFileFullPath, string sheetName,out string errormsg)
{
errormsg = string.Empty;
if (ds.Tables.Count == 0 || string.IsNullOrEmpty(excelFileFullPath))
{
errormsg = "没有数据";
return false;
}
bool bexistfile = false;
if (File.Exists(excelFileFullPath))
{
bexistfile = true;
}
Microsoft.Office.Interop.Excel.Application excelAp = getExcelApplication();
try
{
int rowindex = 1, colindex = 0;
Workbook excelBook;
Worksheet excelSheet;
if (bexistfile)
{
excelSheet = getWorksheet(excelAp, excelFileFullPath, sheetName,out excelBook);
}
else
{
excelBook = excelAp.Workbooks.Add(true);
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[1] as Worksheet;
excelSheet.Name = sheetName;
}
System.Data.DataTable ddt = ds.Tables[0];
foreach (DataColumn col in ddt.Columns)
{
colindex++;
excelSheet.Cells[1, colindex] = col.ColumnName;
}
foreach (DataRow row in ddt.Rows)
{
rowindex++;
colindex = 0;
foreach (DataColumn col in ddt.Columns)
{
colindex++;
excelSheet.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
}
}
XlFileFormat xlff = XlFileFormat.xlWorkbookNormal;
if (excelFileFullPath.EndsWith("xls"))
{
xlff = XlFileFormat.xlExcel8;
}
else if (excelFileFullPath.EndsWith("xlsx"))
{
xlff = XlFileFormat.xlExcel12;
}
if (bexistfile)
{
excelBook.Save();
}
else
{
excelBook.SaveAs(excelFileFullPath, xlff, null, null, false, false, XlSaveAsAccessMode.xlNoChange,
null, null, null, null, null);
}
closeExcel(excelAp);
return true;
}
catch (Exception ex)
{
errormsg = ex.Message;
closeExcel(excelAp);
return false;
}
}
}
}
- c#操作Excel和把Dataset数据写入Excel
- C# 把数据写入Excel
- C#操作Excel(二)写入数据
- C# web 把dataSet数据导出为Excel文件
- C#把DataSet内数据转化为Excel和Word文件的通用类
- C#把DataSet内数据转化为Excel和Word文件的通用类
- C#中NPOI操作excel之读取和写入excel数据
- C#中NPOI操作excel之读取和写入excel数据
- C# 把DataTable数据写入Excel文件的方法
- C# 数据写入Excel表
- 把dataset数据保存到excel,GridView导出到excel
- 使用VSIUAL C#.NET操作Excel -把DataTable中的数据写入Excel
- C#操作Excel文件(读取Excel,写入Excel)
- C#操作Excel文件(读取Excel,写入Excel)
- C#操作Excel文件(读取Excel,写入Excel)
- C#操作Excel文件(读取Excel,写入Excel)
- C#操作Excel文件(读取Excel,写入Excel)
- C#操作Excel文件(读取Excel,写入Excel) .
- 黑马程序员 javascript基础
- swfobject.js 的用法
- 常用的正则表达式集合:
- 在这个社会年轻的我们应该怎么活
- Ext 中Ext.grid.GridPanel样式设置
- c#操作Excel和把Dataset数据写入Excel
- 甘特图在生产制造业中的应用
- 关于数组的初值
- 家乡的小河
- 【IMPDP】使用 TRANSFORM选项去掉表空间和存储子句
- 粘贴 乱码
- 将dll从进程模块列表中移除并保持正常运行
- IMPDP ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified
- plsql工具比较用户对象