最近做项目，涉及到了对Excel的操作，那个急啊》。。现在分享下！
虽然很多东西也是在网上找的资料，为我所用，现在是整理了下的。
首先要导入Excel的com组件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.InteropServices;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office;
using Microsoft.Office.Core;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace Common
{
/// <summary>
/// C# 操作Excel文件
/// </summary>
public class ExcelClass
{
public ExcelClass()
{
}
#region 创建Excel文件
/// <summary>
/// 创建Excel文件
/// </summary>
/// <param name="file_name">路径加文件名</param>
public static void CreateFile(string file_name)
{
DateTime beforeTime;
DateTime afterTime;
beforeTime = DateTime.Now;
//创建Excel文件
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
afterTime = DateTime.Now;
try
{
if (File.Exists(file_name)) //如果文件存在,则删除
{
File.Delete(file_name);
}
object m = System.Reflection.Missing.Value;
Workbook work = excel.Application.Workbooks.Add(true);
excel.Visible = false; //不临时打开Excel
work.RefreshAll(); //刷新Excel
work.SaveAs(file_name, m, m, m, m, m, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m, m, m, m, m);//保存
excel.Quit();
//ExcelClass.Kill(excel); //调用kill方法销毁当前excel进程
GC.Collect(); //强行销毁
}
catch (Exception ex)
{
//excel.Quit();
ExcelClass.Kill(excel);//调用kill方法销毁当前excel进程
//ExcelClass.KillExcel();
//GC.Collect();//强行销毁
throw;
}
finally
{
//ExcelClass.KillExcel(beforeTime, afterTime);
}
}
#endregion
#region 给Excel添加小数的数据有效性
///<summary>
/// 给Excel添加小数的数据有效性
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="IsAdd">该Excel单元格是否设置过正数的有效性</param>
/// <param name="IsBlank">是否能输入空值</param>
/// <param name="StartRange">起始单元格</param>
/// <param name="EndRange">结束单元格</param>
/// <param name="strText1">起始值</param>
/// <param name="strText1">结束值</param>
public static void SetValidateDecimal(Worksheet worksheet, bool IsAdd, bool IsBlank, object StartRange, object EndRange, string strText1, string strText2)
{
try
{
//这里是数据有效性的正数也就是存主键的时候用到，因为一条数据一个主键，不能让他编辑，这样做不到，那么我们可以控制他在这个里面只能输入属于这个主键的数字，比方以下就是代表这条数据的主键是1
if (!IsAdd)
worksheet.get_Range(StartRange, EndRange).Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateDecimal, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText1, strText2);
else
worksheet.get_Range(StartRange, EndRange).Validation.Modify(Microsoft.Office.Interop.Excel.XlDVType.xlValidateDecimal, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText1, strText2);
//不能让主键输入空值
worksheet.get_Range(StartRange, EndRange).Validation.IgnoreBlank = IsBlank;
}
catch (Exception ex)
{
throw;
}
}
#endregion
#region 给Excel添加小数的数据有效性
///<summary>
/// 给Excel添加小数的数据有效性
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="IsAdd">该Excel单元格是否设置过正数的有效性</param>
/// <param name="IsBlank">是否能输入空值</param>
/// <param name="StartRange">起始单元格</param>
/// <param name="EndRange">结束单元格</param>
/// <param name="strText1">起始值</param>
/// <param name="strText1">结束值</param>
/// <param name="strErrorTitle">出错信息标题</param>
/// <param name="strErrorMessage">出错信息内容</param>
public static void SetValidateDecimal(Worksheet worksheet, bool IsAdd, bool IsBlank, object StartRange, object EndRange, string strText1, string strText2, string strErrorTitle, string strErrorMessage)
{
try
{
//这里是数据有效性的正数也就是存主键的时候用到，因为一条数据一个主键，不能让他编辑，这样做不到，那么我们可以控制他在这个里面只能输入属于这个主键的数字
//比方以下就是代表这条数据的主键是1
if (!IsAdd)
worksheet.get_Range(StartRange, EndRange).Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateDecimal, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText1, strText2);
else
worksheet.get_Range(StartRange, EndRange).Validation.Modify(Microsoft.Office.Interop.Excel.XlDVType.xlValidateDecimal, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText1, strText2);
worksheet.get_Range(StartRange, EndRange).Validation.ErrorTitle = strErrorTitle;//出错信息标题
worksheet.get_Range(StartRange, EndRange).Validation.ErrorMessage = strErrorMessage;//出错信息内容
//不能让主键输入空值
worksheet.get_Range(StartRange, EndRange).Validation.IgnoreBlank = IsBlank;
}
catch (Exception ex)
{
throw;
}
}
#endregion
#region 是否隐藏单元格
/// <summary>
/// 是否隐藏单元格,这里是某列至某列的隐藏
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="rows">行</param>
/// <param name="column">列</param>
/// <param name="IsHidden">是否隐藏单元格</param>
public static void RangeHidden(Worksheet worksheet, int rows, int column, bool IsHidden)
{
try
{
//这里是隐藏一列，但还是可以打开Excel后可以用鼠标拖出来，只是虚拟的隐藏了。
//所以在放主键的时候需要设置并且设置每个单元格的数据有效性的正数格式并且不能输入空值
((Range)worksheet.Cells[rows, column]).EntireColumn.Hidden = IsHidden;
}
catch (Exception ex)
{
throw;
}
}
#endregion
#region 给Excel添加下拉框
/// <summary>
/// 给Excel添加下拉框
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="IsAdd">该Excel单元格是否设置过下拉框</param>
/// <param name="IsBlank">是否能输入空值</param>
/// <param name="StartRange">起始单元格</param>
/// <param name="EndRange">结束单元格</param>
/// <param name="strText">下拉框的内容，可用“,”号分隔或者复制Excel中的数据（例如："=$B$1:$B$1000"是代表从B列的第一行复制到1000行）</param>
public static void SetValidateList(Worksheet worksheet, bool IsAdd, bool IsBlank, object StartRange, object EndRange, string strText)
{
try
{
//添加下拉框，注意：选择的列没有设置下拉框用Validation.Add方法，如果设置了你只是修改下拉框用Validation.Modify方法
if (!IsAdd)
worksheet.get_Range(StartRange, EndRange).Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText, Type.Missing);
else
worksheet.get_Range(StartRange, EndRange).Validation.Modify(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText, Type.Missing);
//第一列不能输入空值，注意：这里的单元格数量一定要和设置的数据有效性相同
worksheet.get_Range(StartRange, EndRange).Validation.IgnoreBlank = IsBlank;
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 给Excel添加下拉框
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="IsAdd">该Excel单元格是否设置过下拉框</param>
/// <param name="IsBlank">是否能输入空值</param>
/// <param name="StartRange">起始单元格</param>
/// <param name="EndRange">结束单元格</param>
/// <param name="strText">下拉框的内容，可用“,”号分隔或者复制Excel中的数据（例如："=$B$1:$B$1000"是代表从B列的第一行复制到1000行）</param>
/// <param name="strErrorTitle">出错信息标题</param>
/// <param name="strErrorMessage">出错信息内容</param>
public static void SetValidateList(Worksheet worksheet, bool IsAdd, bool IsBlank, object StartRange, object EndRange, string strText, string strErrorTitle, string strErrorMessage)
{
try
{
//添加下拉框，注意：选择的列没有设置下拉框用Validation.Add方法，如果设置了你只是修改下拉框用Validation.Modify方法
if (!IsAdd)
worksheet.get_Range(StartRange, EndRange).Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText, Type.Missing);
else
worksheet.get_Range(StartRange, EndRange).Validation.Modify(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText, Type.Missing);
worksheet.get_Range(StartRange, EndRange).Validation.ErrorTitle = strErrorTitle;//出错信息标题
worksheet.get_Range(StartRange, EndRange).Validation.ErrorMessage = strErrorMessage;//出错信息内容
//第一列不能输入空值，注意：这里的单元格数量一定要和设置的数据有效性相同
worksheet.get_Range(StartRange, EndRange).Validation.IgnoreBlank = IsBlank;
//注意:下面这句代码是因为考虑到下拉框数据多的话字符长度不够,string只有225字节,在真正用的时候肯定不行的,所以解决办法是先将下拉框数据填写到Excel中的某一列中,然后根据数据的行数选择赋值到数据有效性中。（例如："=$B$1:$B$1000"是代表从B列的第一行复制到1000行）
//worksheet.get_Range(worksheet.Cells[1, 1], column).Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, "=$B$1:$B$1000", Type.Missing);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 给Excel添加下拉框
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="IsAdd">该Excel单元格是否设置过下拉框</param>
/// <param name="IsBlank">是否能输入空值</param>
/// <param name="StartRange">起始单元格</param>
/// <param name="EndRange">结束单元格</param>
/// <param name="strText">下拉框的内容，可用“,”号分隔或者复制Excel中的数据（例如："=$B$1:$B$1000"是代表从B列的第一行复制到1000行）</param>
/// <param name="strErrorTitle">出错信息标题</param>
/// <param name="strErrorMessage">出错信息内容</param>
/// <param name="strInputTitle">点击单元格输出的标题</param>
/// <param name="strInputMessage">点击单元格输出的内容</param>
public static void SetValidateList(Worksheet worksheet, bool IsAdd, bool IsBlank, object StartRange, object EndRange, string strText, string strErrorTitle, string strErrorMessage, string strInputTitle, string strInputMessage)
{
try
{
//添加下拉框，注意：选择的列没有设置下拉框用Validation.Add方法，如果设置了你只是修改下拉框用Validation.Modify方法
if (!IsAdd)
worksheet.get_Range(StartRange, EndRange).Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText, Type.Missing);
else
worksheet.get_Range(StartRange, EndRange).Validation.Modify(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, strText, Type.Missing);
worksheet.get_Range(StartRange, EndRange).Validation.ErrorTitle = strErrorTitle;//出错信息标题
worksheet.get_Range(StartRange, EndRange).Validation.ErrorMessage = strErrorMessage;//出错信息内容
worksheet.get_Range(StartRange, EndRange).Validation.InputTitle = strInputTitle;//点击单元格输出的标题
worksheet.get_Range(StartRange, EndRange).Validation.InputMessage = strInputMessage;//点击单元格输出的内容
//第一列不能输入空值，注意：这里的单元格数量一定要和设置的数据有效性相同
worksheet.get_Range(StartRange, EndRange).Validation.IgnoreBlank = IsBlank;
//注意:下面这句代码是因为考虑到下拉框数据多的话字符长度不够,string只有225字节,在真正用的时候肯定不行的,所以解决办法是先将下拉框数据填写到Excel中的某一列中,然后根据数据的行数选择赋值到数据有效性中。（例如："=$B$1:$B$1000"是代表从B列的第一行复制到1000行）
//worksheet.get_Range(worksheet.Cells[1, 1], column).Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, "=$B$1:$B$1000", Type.Missing);
}
catch (Exception ex)
{
throw;
}
}
#endregion
#region 选取Excel中的整列
/// <summary>
/// 选取Excel中的整列
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="rows">行</param>
/// <param name="column">列</param>
/// <returns></returns>
public static Range GetColumn(Worksheet worksheet, int rows, int column)
{
try
{
return ((Range)worksheet.Cells[rows, column]).EntireColumn;
}
catch (Exception ex)
{
throw;
}
}
#endregion
#region 选取Excel中的整行
/// <summary>
/// 选取Excel中的整行
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="rows">行</param>
/// <param name="column">列</param>
/// <returns></returns>
public static Range GetRows(Worksheet worksheet, int rows, int column)
{
try
{
return ((Range)worksheet.Cells[rows, column]).EntireRow;
}
catch (Exception ex)
{
throw;
}
}
#endregion
#region 绑定数据到下拉框中
/// <summary>
/// 绑定数据到下拉框中
/// </summary>
/// <param name="dt">数据表格DataTable</param>
/// <param name="s_name">列名</param>
/// <param name="worksheet">Worksheet对象</param>
/// <param name="strColoumn">列名</param>
/// <param name="Col">列数</param>
/// <returns></returns>
public static string GetStrName(System.Data.DataTable dt, string s_name, Worksheet worksheet, string strColoumn, int Col)
{
string name = "";
try
{
for (int j = 0; j < dt.Rows.Count; j++)
{
worksheet.Cells[j + 1, Col] = dt.Rows[j][s_name].ToString();
}
ExcelClass.RangeHidden(worksheet, 1, Col, true);
name = "=$" + strColoumn + "$1:$" + strColoumn + "$" + (dt.Rows.Count).ToString() + "";
}
catch (Exception ex)
{
throw;
}
return name;
}
#endregion
#region 关闭Excel进程
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 关闭Excel进程
/// </summary>
/// <param name="excel"></param>
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
int k = 0;
GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
p.Kill(); //关闭进程k
}
#endregion
#region 关闭Excel进程
public static void KillExcel(DateTime beforeTime, DateTime afterTime)
{
System.Diagnostics.Process[] myProcesses;
DateTime startTime;
myProcesses = System.Diagnostics.Process.GetProcessesByName("EXCEL");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (System.Diagnostics.Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
myProcess.Kill();
}
}
#endregion
#region 读取Excel文件，将内容存储在DataSet中
/// <summary>
/// 读取Excel文件，将内容存储在DataSet中
/// </summary>
/// <param name="opnFileName">带路径的Excel文件名</param>
/// <param name="row1">起始行数</param>
/// <param name="row2">终止行数</param>
/// <returns>DataSet</returns>
public static DataSet ExcelToDataSet(string openFileName, int row1, int row2)
{
string strConn = "";
//把所有列都做为字符串来读取，只要添加属性IMEX=1即可
//想把第一行也作为数据行，那我们可以给连接字符串添加一个HDR=No属性
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = "";
OleDbDataAdapter myDa = null;
DataSet ds = new DataSet();
//strExcel = "select * from [sheet1$]";
strExcel = "select * from [Sheet1$A" + row1 + ":J" + row2 + "]"; //只想读取前10列可以用sheet1A:J 如果只想读取A1到B2的内容 sheet1A1:B2
try
{
conn.Open();
myDa = new OleDbDataAdapter(strExcel, strConn);
myDa.Fill(ds);
return ds;
}
catch (Exception ex)
{
return ds;
}
finally
{
conn.Close();
conn.Dispose();
}
}
#endregion
#region 读取Excel文件，将内容存储在DataSet中
/// <summary>
/// 读取Excel文件，将内容存储在DataSet中
/// </summary>
/// <param name="opnFileName">带路径的Excel文件名</param>
/// <param name="col1">起始列名</param>
/// <param name="col1">终止列名</param>
/// <returns>DataSet</returns>
public static DataSet ExcelToDataSet(string openFileName, string col1, string col2)
{
string strConn = "";
//把所有列都做为字符串来读取，只要添加属性IMEX=1即可
//想把第一行也作为数据行，那我们可以给连接字符串添加一个HDR=No属性
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//判断版本信息
if (excel.Version == "11.0") //是2003版
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";
if (excel.Version == "12.0") //是2007版
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileName + ";Extended Properties=/"Excel 12.0;HDR=YES;IMEX=1/"";
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = "";
OleDbDataAdapter myDa = null;
DataSet ds = new DataSet();
//strExcel = "select * from [sheet1$]";
//只想读取前10列可以用sheet1A:J 如果只想读取A1到B2的内容 sheet1A1:B2
strExcel = "select * from [Sheet2$" + col1 + ":" + col2 + "]";
try
{
conn.Open();
myDa = new OleDbDataAdapter(strExcel, strConn);
myDa.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
}
}
#endregion
}
}
8254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
