C#中Excel文件操作方式及操作类

技术2022-05-11 93

Excel文件的操作方式有两种：一种是使用像数据库一样操作，另一种是使用DCOM方式。

第一种方法比较简单，效率也较高，但使用起来功能少，不灵活。使用方式如下：

引入命名空间：using System.Data.OleDb;

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName +";Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(connString); OleDbCommand objCmd = new OleDbCommand(); objCmd.Connection = objConn; objConn.Open();

//建立表结构 insertSql = @"CREATE TABLE Sheet1(column1 varchar,column2 integer)";

objCmd.CommandText = insertSql; objCmd.ExecuteNonQuery();

//建立插入动作的Command insertSql = "INSERT INTO Sheet1(" + temp + ") values('aaaa',102)"; objCmd.CommandText = insertSql; objCmd.ExecuteNonQuery();

完全和对数据库的操作一样。这种操作不能对Excel文件的格式进行设置。

///

第二种操作方式：

1、在引用中添加对Excel的引用

2、需要命名空间

using System;using System.IO;using System.Collections;using System.Threading;using System.Diagnostics;

2、操作方法：

string staFile = ""; //设置要保存的Excel文件的名称 System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog(); sfd.FileName = ""; //sfd.CheckFileExists = true; sfd.CheckPathExists = true; sfd.DefaultExt = "xls"; sfd.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; sfd.InitialDirectory = "D;//"; sfd.RestoreDirectory = true; if(sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK) staFile = sfd.FileName; else return false; if(staFile.IndexOf(".xls") < 0) staFile += ".xls"; 开始操作Excel对象// System.Reflection.Missing oMiss = System.Reflection.Missing.Value;

Excel.ApplicationClass m_objExcel = new Excel.ApplicationClass();//生成Excel对象 m_objExcel.Visible = false;

Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; Excel.Workbook m_objBook = (Excel.Workbook)(m_objBooks.Add(oMiss)); Excel.Worksheet m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;

//********************向Excel文件中写入数据******************************************** //表头 m_objSheet.get_Range((object)"A1",(object)"I1").MergeCells = true;//合并A1到L1的单元格 Excel.Range er = m_objSheet.get_Range((object)"A1",oMiss); er.Value2 = sName + " 在 /"" + sExamName + "/" 中的考试结果细目"; er.HorizontalAlignment = Excel.Constants.xlCenter; //设置单元格的水平对齐方式 er.VerticalAlignment = Excel.Constants.xlBottom; er.Font.Bold = true; //设置字体 er.Font.Size = 16; er.Font.Name = "宋体"; //考生信息 er = m_objSheet.get_Range((object)"A2",oMiss); er.Value2 = "考生信息："; er.Font.Bold = true; er.ColumnWidth = 11;

m_objSheet.get_Range((object)"E2",(object)"F2").MergeCells = true; m_objSheet.get_Range((object)"G2",(object)"H2").MergeCells = true; m_objSheet.get_Range((object)"E3",(object)"F3").MergeCells = true; m_objSheet.get_Range((object)"G3",(object)"H3").MergeCells = true; m_objSheet.get_Range((object)"B2",oMiss).Value2 = "登录帐号"; //为单元格输入文字 m_objSheet.get_Range((object)"C2",oMiss).Value2 = "真实姓名"; m_objSheet.get_Range((object)"D2",oMiss).Value2 = "所在部门"; m_objSheet.get_Range((object)"E2",oMiss).Value2 = "开始时间"; m_objSheet.get_Range((object)"G2",oMiss).Value2 = "结束时间"; m_objSheet.get_Range((object)"I2",oMiss).Value2 = "总成绩"; m_objSheet.get_Range((object)"J2",oMiss).Value2 = "考试状态";

m_objSheet.get_Range((object)"B3",oMiss).Value2 = sUsername; m_objSheet.get_Range((object)"C3",oMiss).Value2 = sName; m_objSheet.get_Range((object)"D3",oMiss).Value2 = sDepartment; m_objSheet.get_Range((object)"E3",oMiss).Value2 = sBTime; m_objSheet.get_Range((object)"G3",oMiss).Value2 = sETime; m_objSheet.get_Range((object)"I3",oMiss).Value2 = sScore; if( sStatus == "1" ) sStatus = "完成"; else if( sStatus == "-1") sStatus = "未完成"; else if(sStatus == "0") sStatus = "未评分"; m_objSheet.get_Range((object)"J3",oMiss).Value2 = sStatus;

//试卷信息 er = m_objSheet.get_Range((object)"A5",oMiss); er.Value2 = "试卷信息："; er.Font.Bold = true;

m_objSheet.get_Range((object)"E5",(object)"F5").MergeCells = true; m_objSheet.get_Range((object)"E6",(object)"F6").MergeCells = true;

m_objSheet.get_Range((object)"B5",oMiss).Value2 = "试卷名称"; m_objSheet.get_Range((object)"C5",oMiss).Value2 = "试卷分数"; m_objSheet.get_Range((object)"D5",oMiss).Value2 = "及格分数"; m_objSheet.get_Range((object)"E5",oMiss).Value2 = "考试时间（分钟）";

m_objSheet.get_Range((object)"B6",oMiss).Value2 = sExamName; m_objSheet.get_Range((object)"C6",oMiss).Value2 = sFS; m_objSheet.get_Range((object)"D6",oMiss).Value2 = sPS; m_objSheet.get_Range((object)"E6",oMiss).Value2 = sTime; //试题细目 er = m_objSheet.get_Range((object)"A8",oMiss); er.Value2 = "试题细目："; er.Font.Bold = true;

m_objSheet.get_Range((object)"A9",oMiss).Value2 = "编号"; m_objSheet.get_Range((object)"B9",oMiss).Value2 = "题型"; m_objSheet.get_Range((object)"C9",oMiss).Value2 = "知识点"; m_objSheet.get_Range((object)"D9",oMiss).Value2 = "题目内容"; m_objSheet.get_Range((object)"E9",oMiss).Value2 = "可选项"; m_objSheet.get_Range((object)"F9",oMiss).Value2 = "标准答案"; m_objSheet.get_Range((object)"G9",oMiss).Value2 = "试题分数"; m_objSheet.get_Range((object)"H9",oMiss).Value2 = "考生答案"; m_objSheet.get_Range((object)"I9",oMiss).Value2 = "考生得分";

//开始对试卷中的每一试题进行输出 object oTemp = ""; int colNum = 0; string strIndex =""; string sAns = "",sSco = "0"; fScore = 0;

XmlNodeList TmNodes = xmlDoc.SelectNodes("//paper/tm"); int iIndex = 10; for(int i=0; i<TmNodes.Count; i++) { colNum = iIndex; oTemp = "A" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 =strIndex = TmNodes[i].SelectSingleNode("exam_gd_tm_id").InnerText; oTemp = "B" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = TmNodes[i].SelectSingleNode("tx_name").InnerText; oTemp = "C" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = TmNodes[i].SelectSingleNode("tm_key").InnerText; oTemp = "D" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = TmNodes[i].SelectSingleNode("title").InnerText; oTemp = "E" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = TmNodes[i].SelectSingleNode("options").InnerText; oTemp = "F" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = TmNodes[i].SelectSingleNode("answers").InnerText; oTemp = "G" + colNum.ToString(); fScore = float.Parse(TmNodes[i].SelectSingleNode("score").InnerText == "" ? "0":TmNodes[i].SelectSingleNode("score").InnerText); fScore *= fRate; m_objSheet.get_Range( oTemp,oMiss).Value2 = fScore.ToString("#,##0.00;(#,##0.00);0"); //考生答案及得分 getUserData(strIndex,ref sAns,ref sSco); oTemp = "H" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = sAns; oTemp = "I" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = sSco;

iIndex ++;

//子题 if(TmNodes[i].ChildNodes.Count > 10) { for(int j=10; j<TmNodes[i].ChildNodes.Count; j++) { XmlNode node = TmNodes[i].ChildNodes[j]; colNum = iIndex; oTemp = "A" + colNum.ToString(); strIndex = node.SelectSingleNode("exam_gd_tm_id").InnerText; m_objSheet.get_Range( oTemp,oMiss).Value2 = strIndex; oTemp = "B" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = node.SelectSingleNode("tx_name").InnerText; oTemp = "C" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = node.SelectSingleNode("tm_key").InnerText; oTemp = "D" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = node.SelectSingleNode("title").InnerText; oTemp = "E" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = node.SelectSingleNode("options").InnerText; oTemp = "F" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = node.SelectSingleNode("answers").InnerText; oTemp = "G" + colNum.ToString(); fScore = float.Parse(node.SelectSingleNode("score").InnerText == "" ? "0":node.SelectSingleNode("score").InnerText); fScore *= fRate; m_objSheet.get_Range( oTemp,oMiss).Value2 = fScore.ToString("#,##0.00;(#,##0.00);0"); //考生答案及得分 getUserData(strIndex,ref sAns,ref sScore); oTemp = "H" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = sAns; oTemp = "I" + colNum.ToString(); m_objSheet.get_Range( oTemp,oMiss).Value2 = sScore;

iIndex ++; } } } //保存并关闭Excel m_objBook.SaveAs(staFile, oMiss, oMiss, oMiss, oMiss,oMiss, Excel.XlSaveAsAccessMode.xlNoChange, oMiss,oMiss,oMiss, oMiss); m_objBook.Close(false, oMiss, oMiss); m_objBooks.Close(); m_objExcel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(er); //这些操作很重要，释放Excel进程 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); GC.Collect();

//******************************

注：以上关于Excel对象里的一些属性如果不全知道的话，可以通过使用Excel文件里的宏得到。

打开一个Excel文件，工具-》宏-》录制新宏。这样以后你对Excel的所有操作都会被记录下来。操作完成后可以查看刚才录制的宏。通过查看宏文件就可以大概知道某一操作对应什么方法了。但要在C#的Excel对象中使用，和宏里的还不大一样。这时你可以通过.net里的对象浏览器来查找对应的方法或属性的命字和使用方法。

专利

最新回复(0)