C# 读写xml、excel、word、access
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了C# 读写xml、excel、word、access,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含19756字,纯文字阅读大概需要29分钟。
内容图文
C# 读写xml、excel、word、access
这里只是起个头,不做深入展开,方便以后用到参考
读写xml,主要使用.net 的xml下的document
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
public static void GetAreaCodes(string path,outstring[] areaCodes ,outstring[] pointCodes) { if (File.Exists(path)) { try { XmlDocument xml = new XmlDocument(); xml.LoadXml(path); List<string> areas = new List<string>(); var areanode = xml.SelectSingleNode("areaRoot"); var elements = areanode.ChildNodes; foreach (var element in elements) { areas.Add((element as XmlElement).InnerXml); } List<string> points = new List<string>(); var pointnode = xml.SelectSingleNode("pointRoot"); elements = areanode.ChildNodes; foreach (var element in elements) { points.Add((element as XmlElement).InnerXml); } areaCodes = areas.ToArray(); pointCodes = points.ToArray(); } catch { pointCodes = new[] { "GPS点", "建屋", "等级公路" }; areaCodes = new[] { "砖房", "阳台", "建屋", "地类界", "变电房" }; } } else { pointCodes = new[] { "GPS点", "建屋", "等级公路" }; areaCodes = new[] { "砖房", "阳台", "建屋", "地类界", "变电房" }; } } publicstaticvoid WriteAreaCodes(string path, string[] areaCodes, string[] pointCodes) { try { XmlDocument xml=new XmlDocument(); XmlDeclaration xmlDeclaration = xml.CreateXmlDeclaration("1.0", "utf-8","yes"); XmlNode Codes = xml.CreateElement("Codes"); xml.AppendChild(xmlDeclaration); xml.AppendChild(Codes); XmlNode areaRoot = xml.CreateElement("areaRoot"); foreach (var str in areaCodes) { XmlElement areaCode = xml.CreateElement("areaCode"); areaCode.InnerXml=str; areaRoot.AppendChild(areaCode); } Codes.AppendChild(areaRoot); XmlNode pointRoot = xml.CreateElement("pointRoot"); foreach (var str in pointCodes) { XmlElement pointCode = xml.CreateElement("pointCode"); pointCode.InnerXml=str; pointRoot.AppendChild(pointCode); } Codes.AppendChild(pointRoot); xml.Save(path); } catch (Exception ex) { throw ex; } }
读写excel,调用com,读写比较慢
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SDataTable = System.Data.DataTable;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Reflection;
public class CExcel { private string filepath = ""; private Microsoft.Office.Interop.Excel.Application excel; private Workbook workbook = null; public CExcel() { excel = new Application(); excel.Visible = false; excel.DisplayAlerts = false; } ///<summary>/// 打开Excel文件 ///</summary>///<param name="filename">文件名</param>publicvoid OpenExcel(string filename) { workbook = excel.Application.Workbooks.Open(filename); } ///<summary>/// 获取一个sheet ///</summary>///<param name="index">sheet索引</param>///<param name="sheetname">sheet名,默认为空,为空以第一个参数为准,否则以名为准</param>///<returns>返回worksheet对象</returns>public Worksheet GetSheet(int index,string sheetname=null) { if (workbook == null) returnnull; var sheet = workbook.Worksheets.get_Item(index); if (sheetname == null) return sheet; foreach (var sh in workbook.Worksheets) { sheet = sh as Worksheet; if (sheet.Name == sheetname) break; } return sheet; } ///<summary>/// 关闭workbook ///</summary>publicvoid Closeworkbook() { if (workbook != null) workbook.Close(); } ///<summary>/// 释放excel对象 ///</summary>publicvoid Dispose() { excel.Quit(); } ///<summary>/// 读取一个sheet内容 ///</summary>///<param name="psheet"></param>///<param name="index"></param>///<returns></returns>public SDataTable GetTableFromSheet(Worksheet psheet, int index = 0) { int rowcount = psheet.UsedRange.Cells.Rows.Count; int colcount = psheet.UsedRange.Columns.Count; Range range; SDataTable dt = new SDataTable(); dt.TableName = psheet.Parent.Name + psheet.Name; DataColumn dc; int ColumnID = 1; int col = 0; while (col <= colcount) { dc = new DataColumn(); dc.DataType = typeof(string); dc.ColumnName = col.ToString(); dt.Columns.Add(dc); col++; } for (int iRow = 1; iRow <= rowcount; iRow++) { var dr = dt.NewRow(); for (int iCol = 1; iCol <= colcount; iCol++) { range = psheet.Cells[iRow, iCol] as Range; string content = ""; if (range.Value != null) { content = range.Value.ToString(); } dr[iCol - 1] = content; } dt.Rows.Add(dr); } return dt; } ///<summary>/// 导出excel ///</summary>///<param name="table"></param>///<param name="filename"></param>publicvoid Datatabletoexcel(string filename, params SDataTable[] tables) { excel.DefaultFilePath = ""; excel.DisplayAlerts = true; excel.SheetsInNewWorkbook = 1; var book = excel.Workbooks.Add(true); foreach (var table in tables) { var result = book.Worksheets.Add(); var sheet=book.ActiveSheet; sheet.Name = table.TableName; int rownum = table.Rows.Count; int colnum = table.Columns.Count; int rowindex = 1; int columnindex = 0; int row = 0; int col = 0; for (int i = 0; i < rownum; i++) { col = 0; for (int j = 0; j < colnum; j++) { sheet.Cells[row + 1, col + 1] = table.Rows[i][j] == null ? "" : table.Rows[i][j].ToString(); col++; } row++; } #region//合并单元格//Range rangeLecture = sheet.Range[sheet.Cells[1, 1], sheet.Cells[2, 1]];//左上和右下 //rangeLecture.MergeCells = true;#endregion } excel.DisplayAlerts = false; book.SaveAs(filename); book.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(book); book = null; GC.Collect(); GC.WaitForPendingFinalizers(); } ///<summary>设置字体 ///</summary>///<param name="sheet"></param>void SetFont(Worksheet sheet) { excel.StandardFont = "宋体"; excel.StandardFontSize = 9; //sheet.Range.AutoFit();//自适应 //sheet.Range[sheet.Cells[1, 1], sheet.Cells[4, 27]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐 //sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowindex, 27]].Borders.LineStyle = 1;//设置边框 } }
用NPOI 读写excel,速度比com快
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using System.Data;
public class NExcel { private HSSFWorkbook hssfworkbook; private string filepath; public void Open(string file) { using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(fs); } } ///<summary>/// 获取sheet ///</summary>///<param name="index"></param>///<param name="sheetname"></param>///<returns></returns>public ISheet getSheet(int index,string sheetname=null) { if(hssfworkbook==null)returnnull; ISheet sheet; if(sheetname==null) { sheet =hssfworkbook.GetSheetAt(index); }else { sheet=hssfworkbook.GetSheet(sheetname); } return sheet; } ///<summary>/// 读取excel文件 ///</summary>///<param name="sheet"></param>///<returns></returns>public DataTable getData(ISheet sheet) { System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(Convert.ToChar(((int)‘A‘) + j).ToString()); } while (rows.MoveNext()) { if (rows.Current == null) continue ; HSSFRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum-1; i++) { var cell = row.GetCell(i); if (cell != null) cell.SetCellType(CellType.String); dr[i] = cell == null ? null : cell.ToString(); } dt.Rows.Add(dr); } return dt; } ///<summary>/// 写excel文件 ///</summary>///<param name="filePath"></param>///<param name="dts"></param>publicvoid WriteExcel( string filePath,params DataTable[] dts) { if (string.IsNullOrEmpty(filePath)) return; NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); foreach (var dt in dts) { if (null == dt && dt.Rows.Count==0)continue; NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); } } } // 写入到客户端 using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } book = null; } } }
读写word 调用com
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Word;
using MSWord = Microsoft.Office.Interop.Word;
public class CSWord { private Application wordApp = null; private Document wordDoc = null; privatestring docPath = null; privateobject Nothing = System.Reflection.Missing.Value; public CSWord(string path,bool isOpen) { try { docPath = path; Object Nothing = System.Reflection.Missing.Value; //创建Word文档if (isOpen) { wordApp = new MSWord.Application(); wordDoc = wordApp.Documents.Open(path); } else { wordApp = new MSWord.Application(); wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing); } wordApp.Visible = false; } catch { thrownew Exception("创建word对象失败"); } } publicvoid SetFont() { wordApp.Selection.Font.Bold = 0; wordApp.Selection.Font.Italic = 0; wordApp.Selection.Font.Subscript = 0; } publicvoid SetFontName(string strType) { wordApp.Selection.Font.Name = strType; } publicvoid SetFontSize(int nSize) { wordApp.Selection.Font.Size = nSize; } publicvoid SetAlignment(WdParagraphAlignment align) { wordApp.Selection.ParagraphFormat.Alignment = align; } publicvoid GoToTheEnd() { object unit; unit = Microsoft.Office.Interop.Word.WdUnits.wdStory; wordApp.Selection.EndKey(ref unit, ref Nothing); } publicvoid GoToTheBeginning() { object unit; unit = Microsoft.Office.Interop.Word.WdUnits.wdStory; wordApp.Selection.HomeKey(ref unit, ref Nothing); } ///<summary>/// 添加文字 ///</summary>///<param name="txtContent"></param>///<param name="Bold"></param>///<param name="size"></param>publicvoid AddText(string txtContent,int Bold=0,int size=14) { //wordApp.Selection.Font.Spacing = 10;//字符间隔 wordApp.Selection.Font.Bold = Bold; wordApp.Selection.Font.Size = size; wordApp.Selection.TypeText(txtContent); wordApp.Selection.TypeParagraph(); } ///<summary>/// 添加图片 ///</summary>///<param name="filepath"></param>publicvoid AddPic(string filepath) { object range = wordDoc.Paragraphs.Last.Range; //定义该图片是否为外s部链接object linkToFile = false;//默认 //定义插入的图片是否随word一起保存object saveWithDocument = true; //向word中写入图片 wordDoc.InlineShapes.AddPicture(filepath, ref Nothing, ref Nothing, ref Nothing); object unite = Microsoft.Office.Interop.Word.WdUnits.wdStory; wordApp.Selection.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter;//居中显示图片 //wordDoc.InlineShapes[1].Height = 130; //wordDoc.InlineShapes[1].Width = 200; wordDoc.Content.InsertAfter("\n"); wordApp.Selection.EndKey(ref unite, ref Nothing); wordApp.Selection.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter; wordApp.Selection.Font.Size = 10;//字体大小 wordApp.Selection.TypeText("图1 测试图片\n"); } ///<summary>/// 添加表 ///</summary>publicvoid AddTable() { int tableRow = 6; int tableColumn = 6; //定义一个word中的表格对象 MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range, tableRow, tableColumn, ref Nothing, ref Nothing); wordDoc.Tables[1].Cell(1, 1).Range.Text = "列\n行"; for (int i = 1; i < tableRow; i++) { for (int j = 1; j < tableColumn; j++) { if (i == 1) { table.Cell(i, j + 1).Range.Text = "Column " + j; } if (j == 1) { table.Cell(i + 1, j).Range.Text = "Row " + i; } table.Cell(i + 1, j + 1).Range.Text = i + "行 " + j + "列"; } } //添加行 table.Rows.Add(ref Nothing); table.Rows[tableRow + 1].Height = 45; //向新添加的行的单元格中添加图片 //string FileName = "d:\\kk.jpg";//图片所在路径object LinkToFile = false; object SaveWithDocument = true; object Anchor = table.Cell(tableRow + 1, tableColumn).Range;//选中要添加图片的单元格 //wordDoc.Application.ActiveDocument.InlineShapes.AddPicture(FileName, ref LinkToFile, ref SaveWithDocument, ref Anchor); //wordDoc.Application.ActiveDocument.InlineShapes[1].Width = 75;//图片宽度 //wordDoc.Application.ActiveDocument.InlineShapes[1].Height = 45;//图片高度 // 将图片设置为四周环绕型 //MSWord.Shape s = wordDoc.Application.ActiveDocument.InlineShapes[1].ConvertToShape(); //s.WrapFormat.Type = MSWord.WdWrapType.wdWrapSquare; //设置table样式 table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast; table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8")); table.Range.Font.Size = 10.5F; table.Range.Font.Bold = 0; table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter; table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalBottom; //设置table边框样式 table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleDouble; table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle; table.Rows[1].Range.Font.Bold = 1; table.Rows[1].Range.Font.Size = 12F; table.Cell(1, 1).Range.Font.Size = 10.5F; wordApp.Selection.Cells.Height = 40;//所有单元格的高度for (int i = 2; i <= tableRow; i++) { table.Rows[i].Height = 20; } table.Cell(1, 1).Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphRight; table.Cell(1, 1).Range.Paragraphs[2].Format.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphLeft; table.Columns[1].Width = 50; for (int i = 2; i <= tableColumn; i++) { table.Columns[i].Width = 75; } //添加表头斜线,并设置表头的样式 table.Cell(1, 1).Borders[Microsoft.Office.Interop.Word.WdBorderType.wdBorderDiagonalDown].Visible = true; table.Cell(1, 1).Borders[Microsoft.Office.Interop.Word.WdBorderType.wdBorderDiagonalDown].Color = Microsoft.Office.Interop.Word.WdColor.wdColorGray60; table.Cell(1, 1).Borders[Microsoft.Office.Interop.Word.WdBorderType.wdBorderDiagonalDown].LineWidth = Microsoft.Office.Interop.Word.WdLineWidth.wdLineWidth050pt; //表格边框 //表格内容行边框 SetTableBorderStyle(table, MSWord.WdBorderType.wdBorderHorizontal, MSWord.WdColor.wdColorGray20, MSWord.WdLineWidth.wdLineWidth025pt); //表格内容列边框 SetTableBorderStyle(table, MSWord.WdBorderType.wdBorderVertical, MSWord.WdColor.wdColorGray20, MSWord.WdLineWidth.wdLineWidth025pt); SetTableBorderStyle(table, MSWord.WdBorderType.wdBorderLeft, MSWord.WdColor.wdColorGray50, MSWord.WdLineWidth.wdLineWidth050pt); SetTableBorderStyle(table, MSWord.WdBorderType.wdBorderRight, MSWord.WdColor.wdColorGray50, MSWord.WdLineWidth.wdLineWidth050pt); SetTableBorderStyle(table, MSWord.WdBorderType.wdBorderTop, MSWord.WdColor.wdColorGray50, MSWord.WdLineWidth.wdLineWidth050pt); SetTableBorderStyle(table, MSWord.WdBorderType.wdBorderBottom, MSWord.WdColor.wdColorGray50, MSWord.WdLineWidth.wdLineWidth050pt); //合并单元格 table.Cell(4, 4).Merge(table.Cell(4, 5));//横向合并 table.Cell(2, 3).Merge(table.Cell(4, 3));//纵向合并 } publicvoid OpenModel(string modelPath) { object ModelName = modelPath; wordDoc = wordApp.Documents.Open(ref ModelName);//打开word模板 //下面操作模板。。。。 } privatevoid SetTableBorderStyle(Table table1,MSWord.WdBorderType bdType,MSWord.WdColor color,MSWord.WdLineWidth width) { table1.Borders[bdType].Visible = true; table1.Borders[bdType].Color = color; table1.Borders[bdType].LineWidth = width; } ///<summary>/// 保存 ///</summary>///<returns></returns>publicbool Save() { try { object path = docPath; object format = MSWord.WdSaveFormat.wdFormatDocument; wordDoc.SaveAs(ref path, ref format); wordDoc.Close(ref Nothing, ref Nothing, ref Nothing); wordApp.Quit(ref Nothing, ref Nothing, ref Nothing); returntrue; } catch { returnfalse; } } ///<summary>/// 替换 ///</summary>///<param name="strOldText"></param>///<param name="strNewText"></param>publicvoid Replace(string strOldText, string strNewText) { this.wordApp.Selection.Find.ClearFormatting();//移除Find的搜索文本和段落格式设置 wordApp.Selection.Find.Text = strOldText;//需要查找的字符if (wordApp.Selection.Find.Execute())//查找字符 { wordApp.Selection.TypeText(strNewText);//在找到的字符区域写数据 } } ///<summary>///查找字符 ///</summary>///<returns></returns>publicbool FindStr(string str) { Find find = wordApp.Selection.Find; Object findText; Object matchCase = Type.Missing; Object matchWholeWord = Type.Missing; Object matchWildcards = Type.Missing; Object matchSoundsLike = Type.Missing; Object matchAllWordForms = Type.Missing; Object forward = true; Object wrap = WdFindWrap.wdFindStop; Object format = Type.Missing; Object replaceWith = Type.Missing; Object replace = Type.Missing; Object matchKashida = Type.Missing; Object matchDiacritics = Type.Missing; Object matchAlefHamza = Type.Missing; Object matchControl = Type.Missing; if ((str == "") || (str == string.Empty)) findText = find.Text; else findText = str; find.ClearFormatting(); return find.Execute(ref findText, ref matchCase, ref matchWholeWord, ref matchWildcards, ref matchSoundsLike, ref matchAllWordForms, ref forward, ref wrap, ref format, ref replaceWith, ref replace, ref matchKashida, ref matchDiacritics, ref matchAlefHamza, ref matchControl); } ///<summary>/// 保存成HTML ///</summary>///<param name="strFileName"></param>publicvoid SaveAsHtml(string strFileName) { Type wordType = wordApp.GetType(); object missing = System.Reflection.Missing.Value; object fileName = strFileName; object Format = (int)Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatHTML; wordDoc.SaveAs(ref fileName, ref Format, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing); } publicvoid Dispose() { if (wordDoc != null) { wordDoc.Close(); } if (wordApp != null) { wordApp.Quit(); } } privatevoid test() { Paragraphs pgs = wordApp.Selection.Paragraphs; foreach (Paragraph pg in pgs) { Console.WriteLine(pg.Range.Text); } } }
读写access ,调用OleDb
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.OleDb; using System.IO; using System.Data; namespace CSharpTest { public class CAccessClass { private OleDbConnection DbConnection; private const string connect = "provider=microsoft.jet.oledb.4.0;Data Source="; privatestaticvoid log(string msg) { using (StreamWriter sw = new StreamWriter("log.txt", true)) { sw.WriteLine(String.Format("{0}:{1}", DateTime.Now.ToString(), msg)); } } public CAccessClass(string Path) { DbConnection = new OleDbConnection(connect + Path); } public DataTable SelectData(string sql) { try { DbConnection.Open(); var myadapter = new OleDbDataAdapter(); myadapter.SelectCommand = new OleDbCommand(sql, DbConnection); DataSet ds = new DataSet(); myadapter.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { returnnull; } finally { DbConnection.Close(); } } publicint Delete(string sql) { try { DbConnection.Open(); var cmd = new OleDbCommand(sql, DbConnection); return cmd.ExecuteNonQuery(); } catch (Exception ex) { return0; } finally { DbConnection.Close(); } } publicstaticvoid Test(string path=null) { if(path==null) path = @"D:\WORK\Project\苗尾\BGKDB.MDB"; CAccessClass cac = new CAccessClass(path); string sql = "select * from Sensor where SensorType=‘钢筋计‘"; var table = cac.SelectData(sql); for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Columns.Count; j++) { Console.Write(table.Rows[i][j]+""); } Console.WriteLine(); } Console.WriteLine(String.Format("获取到{0}条数据", table.Rows.Count)); } } }
原文:http://www.cnblogs.com/onegarden/p/5895652.html
内容总结
以上是互联网集市为您收集整理的C# 读写xml、excel、word、access全部内容,希望文章能够帮你解决C# 读写xml、excel、word、access所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。