程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> 關於.NET >> C# 讀寫xml、excel、word、access,

C# 讀寫xml、excel、word、access,

編輯:關於.NET

C# 讀寫xml、excel、word、access,


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,out string[] areaCodes ,out string[] 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[] { "磚房", "陽台", "建屋", "地類界", "變電房" }; } } public static void 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> public void 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) return null; 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> public void Closeworkbook() { if (workbook != null) workbook.Close(); } /// <summary> /// 釋放excel對象 /// </summary> public void 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> public void 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)return null; 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> public void 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; private string docPath = null; private object 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 { throw new Exception("創建word對象失敗"); } } public void SetFont() { wordApp.Selection.Font.Bold = 0; wordApp.Selection.Font.Italic = 0; wordApp.Selection.Font.Subscript = 0; } public void SetFontName(string strType) { wordApp.Selection.Font.Name = strType; } public void SetFontSize(int nSize) { wordApp.Selection.Font.Size = nSize; } public void SetAlignment(WdParagraphAlignment align) { wordApp.Selection.ParagraphFormat.Alignment = align; } public void GoToTheEnd() { object unit; unit = Microsoft.Office.Interop.Word.WdUnits.wdStory; wordApp.Selection.EndKey(ref unit, ref Nothing); } public void 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> public void 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> public void 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> public void 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));//縱向合並 } public void OpenModel(string modelPath) { object ModelName = modelPath; wordDoc = wordApp.Documents.Open(ref ModelName);//打開word模板 //下面操作模板。。。。 } private void 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> public bool 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); return true; } catch { return false; } } /// <summary> /// 替換 /// </summary> /// <param name="strOldText"></param> /// <param name="strNewText"></param> public void 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> public bool 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> public void 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); } public void Dispose() { if (wordDoc != null) { wordDoc.Close(); } if (wordApp != null) { wordApp.Quit(); } } private void 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=";
        private static void 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)
            {
                return null;

            }
            finally
            {
                DbConnection.Close();
            }


        }
      
        public int Delete(string sql)
        {
            try
            {
                DbConnection.Open();
                var cmd = new OleDbCommand(sql, DbConnection);
                return cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                DbConnection.Close();
            }


        }


        public static void 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));

            
        }


    }
}

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved