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 } }