using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
namespace PublicMod
{
public class CExcel
{
/// <summary>
/// 查询类
/// </summary>
/// <param name="excelFile">excel文件完整路径</param>
public CExcel(string excelFile, string sheetname)
{
GC.Collect();
this.excelFile = excelFile;
this.sheetName = sheetname;
try
{
excelConString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties='Excel 8.0;imex=1'", excelFile);
excelConnnection = new OleDbConnection(excelConString);
app = new Excel.Application();
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
app.UserControl = true;//如果只想用程序控制该excel而不想让用户操作时候,可以设置为false
workbook = app.Workbooks.Open(excelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.ReadOnlyRecommended = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 判断制定的工作簿中的表是否存在
/// </summary>
/// <returns></returns>
public bool HaveSheet()
{
//for (int j = 1; j <= workbook.Sheets.Count; j++)
//{
// if (((Excel.Worksheet)workbook.Sheets[j]).Name == sheetName)
// {
// sMessageBox.Warm("");
// }
//}
for (int i = 1; i <= workbook.Worksheets.Count; i++)
{
if (((Excel.Worksheet)workbook.Worksheets[i]).Name == sheetName)
{
return true;
}
}
return false;
}
private string sheetName;
private string excelFile;
private string excelConString;
private Excel.Application app;
private Excel.Workbook workbook;
public OleDbConnection excelConnnection;
/// <summary>
/// 查询全部数据
/// </summary>
/// <returns></returns>
public DataTable getAllDate()
{
DataTable dt = null;
string sql = string.Format("select * from [{0}$] ", sheetName);
try
{
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, excelConnnection);
adapter.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excelConnnection.Close();
}
return dt;
}
/// <summary>
/// 获取excel中有多少行数据
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public int rowCount()
{
int num = 0;
string sql = string.Format("select count(*) from [{0}$]", sheetName);
try
{
OleDbCommand command = new OleDbCommand(sql, excelConnnection);
excelConnnection.Open();
num = (Int32)command.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excelConnnection.Close();
}
return num;
}
/// <summary>
/// 获取excel中有多少列数据
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public int columnCount()
{
int num = 0;
string sql = string.Format("select * from [{0}$] ", sheetName);
try
{
OleDbCommand command = new OleDbCommand(sql, excelConnnection);
excelConnnection.Open();
OleDbDataReader dr = command.ExecuteReader();
dr.Read();
num = dr.FieldCount;
dr.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excelConnnection.Close();
}
return num;
}
/// <summary>
/// 获取指定单元格的文本
/// </summary>
/// <param name="sheetName"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
public string GetCellStr(int row, int col)
{
string str = "";
try
{
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[sheetName];
Excel.Range r = (Excel.Range)(worksheet.Cells[row, col]);
str = r.Text.ToString();
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
return str;
}
public void Close()
{
try
{
if (excelConnnection.State == ConnectionState.Open)
{
excelConnnection.Close();
excelConnnection.Dispose();
}
workbook.Save();
workbook = null;
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
System.GC.Collect();
}
catch { }
}
/// <summary>
/// 单元格字体颜色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void CellsFontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Worksheet myExcel = (Excel.Worksheet)workbook.Worksheets[sheetName];
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Color = color;//设置字体颜色
}
/// <summary>
/// 单元格字体颜色及填充方式
/// </summary>
/// <param name="row">行</param>
/// <param name="column">列</param>
public void CellsFontColor(int row, int column, ColorIndex color)
{
Excel.Worksheet myExcel = (Excel.Worksheet)workbook.Worksheets[sheetName];
Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);
range.Font.ColorIndex = color;//设置字体颜色
}
public void ExcelShow()
{
workbook.Save();
app.Visible = true;
}
}
/// <summary>
/// 单元格填充方式
/// </summary>
public enum Pattern
{
Automatic = -4105,
Checker = 9,
CrissCross = 16,
Down = -4121,
Gray16 = 17,
Gray25 = -4124,
Gray50 = -4125,
Gray75 = -4126,
Gray8 = 18,
Grid = 15,
Horizontal = -4128,
LightDown = 13,
LightHorizontal = 11,
LightUp = 14,
LightVertical = 12,
None = -4142,
SemiGray75 = 10,
Solid = 1,
Up = -4162,
Vertical = -4166
}
/// <summary>
/// 常用颜色定义,对就Excel中颜色名
/// </summary>
public enum ColorIndex
{
无色= -4142,
自动= -4105,
黑色= 1,
褐色= 53,
橄榄= 52,
深绿= 51,
深青= 49,
深蓝= 11,
靛蓝= 55,
灰色= 56,
深红= 9,
橙色= 46,
深黄= 12,
绿色= 10,
青色= 14,
蓝色= 5,
蓝灰= 47,
灰色= 16,
红色= 3,
浅橙色= 45,
酸橙色= 43,
海绿= 50,
水绿色= 42,
浅蓝= 41,
紫罗兰= 13,
灰色= 48,
粉红= 7,
金色= 44,
黄色= 6,
鲜绿= 4,
青绿= 8,
天蓝= 33,
梅红= 54,
灰色= 15,
玫瑰红= 38,
茶色= 40,
浅黄= 36,
浅绿= 35,
浅青绿= 34,
淡蓝= 37,
淡紫= 39,
白色= 2
}
}