DataSet序列化和反序列化以及压缩算法的例子

【实例简介】

这是DataSet序列化和反序列化以及压缩算法的例子

【实例截图】

【核心代码】

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Runtime.Serialization;
using System.IO.Compression;

using DocumentFormat.OpenXml.Extensions;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;

namespace SerializationTest
{
	class Program
	{
		static void Main(string[] args)
		{
            string xlfile=@"C:\Users\Ralph\Documents\visual studio 2010\Projects\SerializationTest\data.xlsx";
            string datafile=@"C:\Users\Ralph\Documents\visual studio 2010\Projects\SerializationTest\data.dat";

            DataSet ds = new DataSet();
            ds.Tables.Add(OpenXMLExcelHelper.GetDataTable(xlfile,"Sheet1"));

            DataTable dt;
            dt = ds.Tables[0];

            BinaryDataSetSerializer.Serialize(ds, datafile);
            BinaryDataSetSerializer.ZipSerialize(ds, datafile   "z");

            //DataSet ds2 = BinaryDataSetSerializer.Deserialize(datafile);
            DataSet ds2 = BinaryDataSetSerializer.ZipDeserialize(datafile   "z");

            dt = ds2.Tables[0];

            foreach (DataRow row in dt.Rows)  
            {  
                //Console.WriteLine("/t");  
                foreach (DataColumn column in dt.Columns)  
                    Console.Write("\t{0}", row[column]);  
                Console.WriteLine("\t");  
            }  

            Console.ReadKey();
		}
    }

    public static class OpenXMLExcelHelper
    {
        public static DataTable GetDataTable(string excelFile, string sheetName)
        {
            using (var doc = SpreadsheetDocument.Open(excelFile, false))
            {
                WorkbookPart wbPart = doc.WorkbookPart;
                List<Sheet> sheets = wbPart.Workbook.Descendants<Sheet>().ToList();
                var iSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(c => c.Name == sheetName);
                WorksheetPart worksheetPart = (WorksheetPart)wbPart.GetPartById(iSheet.Id);

                if (iSheet != null)
                {
                    SharedStringTable stringTable = doc.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();
                    DataTable dt = new DataTable("Excel");
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == 1)
                        {
                            GetDataColumn(row, stringTable, ref dt);
                        }
                        GetDataRow(row, stringTable, ref dt);
                    }
                    return dt;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary> 
        /// 构建DataTable的列         
        /// </summary>         
        /// <param name="row">OpenXML定义的Row对象</param>         
        /// <param name="stringTablePart"></param>         
        /// <param name="dt">需要返回的DataTable对象</param>         
        /// <returns></returns> 

        private static void GetDataColumn(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataColumn col = new DataColumn();
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                col = new DataColumn(cellVal);
                dt.Columns.Add(col);
            }
        }

        /// <summary>         
        /// 构建DataTable的每一行数据，并返回该Datatable         
        /// </summary>         
        /// <param name="row">OpenXML的行</param>         
        /// <param name="stringTablePart"></param>         
        /// <param name="dt">DataTable</param>         
        private static void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            // 读取算法：按行逐一读取单元格，如果整行均是空数据 
            // 则忽略改行（因为本人的工作内容不需要空行）-_- 
            DataRow dr = dt.NewRow();
            int i = 0;
            int nullRowCount = i;
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                if (cellVal == string.Empty)
                {
                    nullRowCount  ;
                }
                dr[i] = cellVal;
                i  ;
            }

            if (nullRowCount != i)
            {
                dt.Rows.Add(dr);
            }

        }



        /// <summary> 
        /// 获取单元格的值 
        /// </summary> 
        /// <param name="cell"></param> 
        /// <param name="stringTablePart"></param> 
        /// <returns></returns> 
        public static string GetValue(Cell cell, SharedStringTable stringTable)
        {
            //由于Excel的数据存储在SharedStringTable中，需要获取数据在SharedStringTable 中的索引 
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                    return value;
                value = double.Parse(cell.CellValue.InnerText).ToString();
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTable.ChildElements[Int32.Parse(value)].InnerText;
                }
            }
            catch (Exception)
            {
                value = "N/A";
            }
            return value;
        }


    }

	public static class BinaryDataSetSerializer
	{
		/// <summary>
        /// 序列化DataSet对象并压缩
        /// </summary>
        /// <param name='ds'></param>
        public static bool ZipSerialize(DataSet ds, string filename)
        {
            bool _state = true;
            try
            {
                IFormatter formatter = new BinaryFormatter();//定义BinaryFormatter以序列化DataSet对象
                MemoryStream ms = new MemoryStream();//创建内存流对象
                formatter.Serialize(ms, ds);//把DataSet对象序列化到内存流
                byte[]buffer = ms.ToArray();//把内存流对象写入字节数组
                ms.Close();//关闭内存流对象
                ms.Dispose();//释放资源
                if(File.Exists(filename))
                    File.Delete(filename);
                Stream _Stream = File.Open(filename, FileMode.Create);//创建文件
                GZipStream gzipStream = new GZipStream(_Stream, CompressionMode.Compress, true);//创建压缩对象
                gzipStream.Write(buffer, 0, buffer.Length);//把压缩后的数据写入文件
                gzipStream.Close();//关闭压缩流,这里要注意：一定要关闭，要不然解压缩的时候会出现小于4K的文件读取不到数据，大于4K的文件读取不完整
                gzipStream.Dispose();//释放对象
                _Stream.Flush();//释放内存
                _Stream.Close();//关闭流
                _Stream.Dispose();//释放对象
            }
            catch (Exception ex)
            {
                _state = false;
            }
            return _state;
        }
        /// <summary>
        /// 不压缩直接序列化DataSet
        /// </summary>
        public static bool Serialize(DataSet ds, string filename)
        {
            bool _state = true;
            try
            {
                IFormatter formatter = new BinaryFormatter();//定义BinaryFormatter以序列化DataSet对象
                if (File.Exists(filename))
                    File.Delete(filename);
                Stream _Stream = File.Open(filename, FileMode.Create);//创建文件
                formatter.Serialize(_Stream, ds);//把DataSet对象序列化到文件
                _Stream.Flush();//释放内存
                _Stream.Close();//关闭流
                _Stream.Dispose();//释放对象
            }
            catch (Exception ex)
            {
                _state = false;
            }
            return _state;
        }
        /// <summary>
        /// 反序列化压缩的DataSet
        /// </summary>
        /// <returns></returns>
        public static DataSet ZipDeserialize(string datafile)
        {
            Stream _Stream = File.Open(datafile, FileMode.Open);//打开文件
            _Stream.Position = 0;//设置文件流的位置
            GZipStream gzipStream = new GZipStream(_Stream, CompressionMode.Decompress);//创建解压对象
            byte[] buffer = new byte[4096];//定义数据缓冲
            int offset = 0;//定义读取位置
            MemoryStream ms = new MemoryStream();//定义内存流
            while ((offset = gzipStream.Read(buffer, 0, buffer.Length)) != 0)
            {
            ms.Write(buffer, 0, offset);//解压后的数据写入内存流
            }
            IFormatter formatter = new BinaryFormatter();//定义BinaryFormatter以反序列化DataSet对象
            ms.Position = 0;//设置内存流的位置
            DataSet ds;
            try
            {
            ds = (DataSet)formatter.Deserialize(ms);//反序列化
            }
            catch
            {
            ds = null;
            }
            ms.Close();//关闭内存流
            ms.Dispose();//释放资源
            _Stream.Flush();//释放内存
            _Stream.Close();//关闭文件流
            _Stream.Dispose();//释放资源
            gzipStream.Close();//关闭解压缩流
            gzipStream.Dispose();//释放资源
            return ds;
        }
        /// <summary>
        /// 反序列化未压缩的DataSet
        /// </summary>
        /// <returns></returns>
        public static DataSet Deserialize(string datafile)
        {
            Stream _Stream = File.Open(datafile, FileMode.Open);//打开文件
            _Stream.Position = 0;//设置文件流的位置
            IFormatter formatter = new BinaryFormatter();//定义BinaryFormatter以反序列化DataSet对象
            DataSet ds;
            try
            {
            ds = (DataSet)formatter.Deserialize(_Stream);//反序列化
            }
            catch
            {
            ds = null;
            }
            _Stream.Flush();//释放内存
            _Stream.Close();//关闭文件流
            _Stream.Dispose();//释放资源
            return ds;
        }
     }
}