Csharp/C#教程：OpenXml读写Excel实例代码分享

新版本的xlsx是使用新的存储格式，貌似是处理过的XML。

对于OpenXML我网上搜了一下，很多人没有介绍。所以我就这里推荐下，相信会成为信息系统开发的必备。

先写出个例子，会发现如此的简介：

代码如下:
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingXFormular.config;
usingSystem.IO;
usingcom.xtar.amfx;
usingSystem.Runtime.Serialization.Formatters.Binary;
usingSystem.Data;

namespaceXFormular.test
{
   classClass1
   {
       publicvoidtest()
       {
           DataTabletable=newDataTable(“1”);
           table.Columns.Add(“2”);
           for(inti=0;i<10;i++)
           {
               DataRowrow=table.NewRow();
               row[0]=i;
               table.Rows.Add(row);
           }

           List<DataTable>lsit=newList<DataTable>();
           lsit.Add(table);

           OpenXmlSDKExporter.Export(AppDomain.CurrentDomain.BaseDirectory+”\excel.xlsx”,lsit);
       }
   }
}

写出代码

代码如下:
usingSystem;
usingSystem.IO;
usingSystem.Windows.Forms;
usingDocumentFormat.OpenXml;
usingDocumentFormat.OpenXml.Packaging;
usingDocumentFormat.OpenXml.Spreadsheet;
usingDocumentFormat.OpenXml.Extensions;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Text.RegularExpressions;

namespaceXFormular
{
   classOpenXmlSDKExporter
   {
       privatestaticstring[]Level={“A”,”B”,”C”,”D”,”E”,”F”,”G”,
   “H”,”I”,”G”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,
   “U”,”V”,”W”,”X”,”Y”,”Z”};

       publicstaticList<DataTable>Import(stringpath)
       {
           List<DataTable>tables=newList<DataTable>();

           if(path.EndsWith(ExcelHelper.POSTFIX_SVN))
               returntables;

           using(MemoryStreamstream=SpreadsheetReader.StreamFromFile(path))
           {
               using(SpreadsheetDocumentdoc=SpreadsheetDocument.Open(stream,true))
               {
                   foreach(Sheetsheetindoc.WorkbookPart.Workbook.Descendants<Sheet>())
                   {
                       DataTabletable=newDataTable(sheet.Name.Value);

                       WorksheetPartworksheet=(WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);

                       List<string>columnsNames=newList<string>();

                       foreach(Rowrowinworksheet.Worksheet.Descendants<Row>())
                       {
                           foreach(Cellcellinrow)
                           {
                               stringcolumnName=Regex.Match(cell.CellReference.Value,”[a-zA-Z]+”).Value;

                               if(!columnsNames.Contains(columnName))
                               {
                                   columnsNames.Add(columnName);
                               }

                           }
                       }

                       columnsNames.Sort(CompareColumn);

                       foreach(stringcolumnNameincolumnsNames)
                       {
                           table.Columns.Add(columnName);
                       }

                       foreach(Rowrowinworksheet.Worksheet.Descendants<Row>())
                       {
                           DataRowtableRow=table.NewRow();
                           table.Rows.Add(tableRow);

                           foreach(Cellcellinrow)
                           {
                               stringcolumnName=Regex.Match(cell.CellReference.Value,”[a-zA-Z]+”).Value;
                               tableRow[columnName]=GetValue(cell,doc.WorkbookPart.SharedStringTablePart);
                           }
                       }

                       if(table.Rows.Count<=0)
                           continue;
                       if(table.Columns.Count<=0)
                           continue;

                       tables.Add(table);
                   }
               }
           }

           returntables;
       }

       publicstaticStringGetValue(Cellcell,SharedStringTablePartstringTablePart)
       {

           if(cell.ChildElements.Count==0)

               returnnull;

           //getcellvalue

           Stringvalue=cell.CellValue.InnerText;

           //Lookuprealvaluefromsharedstringtable

           if((cell.DataType!=null)&&(cell.DataType==CellValues.SharedString))

               value=stringTablePart.SharedStringTable

               .ChildElements[Int32.Parse(value)]

               .InnerText;

           returnvalue;

       }

       publicstaticvoidExport(stringpath,List<DataTable>tables)
       {
           using(MemoryStreamstream=SpreadsheetReader.Create())
           {
               using(SpreadsheetDocumentdoc=SpreadsheetDocument.Open(stream,true))
               {
                   SpreadsheetWriter.RemoveWorksheet(doc,”Sheet1″);
                   SpreadsheetWriter.RemoveWorksheet(doc,”Sheet2″);
                   SpreadsheetWriter.RemoveWorksheet(doc,”Sheet3″);

                   foreach(DataTabletableintables)
                   {
                       WorksheetPartsheet=SpreadsheetWriter.InsertWorksheet(doc,table.TableName);
                       WorksheetWriterwriter=newWorksheetWriter(doc,sheet);

                       SpreadsheetStylestyle=SpreadsheetStyle.GetDefault(doc);

                       foreach(DataRowrowintable.Rows)
                       {
                           for(inti=0;i<table.Columns.Count;i++)
                           {
                               stringcolumnName=SpreadsheetReader.GetColumnName(“A”,i);
                               stringlocation=columnName+(table.Rows.IndexOf(row)+1);
                               writer.PasteText(location,row[i].ToString(),style);
                           }
                       }

                       writer.Save();
                   }
                   SpreadsheetWriter.StreamToFile(path,stream);//保存到文件中
               }
           }
       }

       privatestaticintCompareColumn(stringx,stringy)
       {
           intxIndex=Letter_to_num(x);
           intyIndex=Letter_to_num(y);
           returnxIndex.CompareTo(yIndex);
       }

       ///<summary>
       ///数字26进制，转换成字母，用递归算法
       ///</summary>
       ///<paramname=”value”></param>
       ///<returns></returns>
       privatestaticstringNum_to_letter(intvalue)
       {
           //此处判断输入的是否是正确的数字，略（正在表达式判断）
           intremainder=value%26;
           //remainder=(remainder==0)?26:remainder;
           intfront=(value-remainder)/26;
           if(front<26)
           {
               returnLevel[front-1]+Level[remainder];
           }
           else
           {
               returnNum_to_letter(front)+Level[remainder];
           }
           //return””;
       }

       ///<summary>
       ///26进制字母转换成数字
       ///</summary>
       ///<paramname=”letter”></param>
       ///<returns></returns>
       privatestaticintLetter_to_num(stringstr)
       {
           //此处判断是否是由A-Z字母组成的字符串，略（正在表达式片段）
           char[]letter=str.ToCharArray();//拆分字符串
           intreNum=0;
           intpower=1;//用于次方算值
           inttimes=1; //最高位需要加1
           intnum=letter.Length;//得到字符串个数
           //得到最后一个字母的尾数值
           reNum+=Char_num(letter[num-1]);
           //得到除最后一个字母的所以值,多于两位才执行这个函数
           if(num>=2)
           {
               for(inti=num-1;i>0;i–)
               {
                   power=1;//致1，用于下一次循环使用次方计算
                   for(intj=0;j<i;j++)          //幂，j次方，应该有函数
                   {
                       power*=26;
                   }
                   reNum+=(power*(Char_num(letter[num-i-1])+times)); //最高位需要加1，中间位数不需要加一
                   times=0;
               }
           }
           //Console.WriteLine(letter.Length);
           returnreNum;
       }

       ///<summary>
       ///输入字符得到相应的数字，这是最笨的方法，还可用ASIICK编码；
       ///</summary>
       ///<paramname=”ch”></param>
       ///<returns></returns>
       privatestaticintChar_num(charch)
       {
           switch(ch)
           {
               case’A’:
                   return0;
               case’B’:
                   return1;
               case’C’:
                   return2;
               case’D’:
                   return3;
               case’E’:
                   return4;
               case’F’:
                   return5;
               case’G’:
                   return6;
               case’H’:
                   return7;
               case’I’:
                   return8;
               case’J’:
                   return9;
               case’K’:
                   return10;
               case’L’:
                   return11;
               case’M’:
                   return12;
               case’N’:
                   return13;
               case’O’:
               &n bsp;   return14;
               case’P’:
                   return15;
               case’Q’:
                   return16;
               case’R’:
                   return17;
               case’S’:
                   return18;
               case’T’:
                   return19;
               case’U’:
                   return20;
               case’V’:
                   return21;
               case’W’:
                   return22;
               case’X’:
                   return23;
               case’Y’:
                   return24;
               case’Z’:
                   return25;
           }
           return-1;
       }
   }
}

代码如下:
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Data;
usingSystem.Data.OleDb;

namespacextar_biz_codegen
{
   classExcelHelper
   {
       publicstaticstringPOSTFIX_97=”XLS”;

       publicstaticstringPOSTFIX_03=”XLSX”;
   }
}

您可能感兴趣的文章:C#采用OpenXml给word里面插入图片C#采用OpenXml给Word文档添加表格C#采用OpenXml实现给word文档添加文字C#利用Openxml读取Excel数据实例OpenXml合并Table单元格代码实例

c语言10个经典小程序

关于在MFC中将窗口最小化到托盘实现原理及操作步骤

上述就是C#学习教程：OpenXml读写Excel实例代码分享的全部内容，如果对大家有所用处且需要了解更多关于C#学习教程，希望大家多多关注—计算机技术网(www.ctvol.com)!