Silverlight程序中导入Excel2007

这是一篇转载的文章，前段时间需要一个功能，将EXCEL导入到Silverlight程序中，在网上找了很久，终于找到了。

前台XAML，主要是用一个DataGrid来展示导入的Excel数据：

<Grid x:Name="LayoutRoot" Background="White" Height="600">
        <StackPanel>
            
            <ComboBox x:Name="FileNames" Visibility="Collapsed" SelectionChanged="FileNames_SelectionChanged" Width="150" Height="23" HorizontalAlignment="left" VerticalAlignment="Center"></ComboBox>

            <Button x:Name="OKButton" Content="Open Excel" Click="OKButton_Click"  Width="105" Height="23" HorizontalAlignment="Left" VerticalAlignment="Top"/>
            <TextBox x:Name="rowID" Text="1" Width="105" Height="23" HorizontalAlignment="Left" VerticalAlignment="Top" />
            <Button x:Name="ConfirmButton" Visibility="Collapsed" Content="Open WorkSheet" Click="ConfirmButton_Click" Width="105" Height="23" HorizontalAlignment="left" VerticalAlignment="Bottom"/>
        </StackPanel>
        <StackPanel>
            <data:DataGrid x:Name="TheGrid" VerticalScrollBarVisibility="Visible" AutoGenerateColumns="False" Width="800" Height="600">
            </data:DataGrid>
        </StackPanel>
    </Grid>

 

注意要设置AutoGenerateColumns="False"，否则结果会多显示一列。

后台：MainPage.cs

public partial class MainPage : UserControl
    {
        public ExcelClass excelclass;

        String current = null;
        List<Stream> streams = new List<Stream>();
        List<string> stringTable = new List<string>();
        IEnumerable<List<string>> returnedlist = new List<List<string>>();
        List<string> listSheetNames = new List<string>();
        Stream getStyles = null;
        IEnumerable<List<string>> headers = new List<List<string>>();
        int getRowID = 0;
        bool loadingHeaders = false;
        public string name;

        public MainPage()
        {
            InitializeComponent();
        }

        // Executes when the user navigates to this page.
       

        //打开EXCEL文件
        private void OKButton_Click(object sender, RoutedEventArgs e)
        {
            getRowID = Convert.ToInt16(rowID.Text.ToString());
            listSheetNames.Clear();
            streams.Clear();
            FileNames.ItemsSource = null;
            Stream Wookbooknames = null;

            try
            {
                OpenFileDialog dialog = new OpenFileDialog();
                dialog.Filter = "xlsx (*.xlsx) |*.xlsx|*.*()|*.*";
                UnZipper unzip;

                if ((bool)dialog.ShowDialog())
                {
                    Stream s = dialog.File.OpenRead();
                    unzip = new UnZipper(s);
                    Stream sharedStringsStream = s;
                    Stream WorkSheetStream = s;

                    foreach (string filename in unzip.GetFileNamesInZip())
                    {
                        Stream stream = unzip.GetFileStream(filename);
                        String filenameCompare = filename;
                        StreamReader reader = new StreamReader(stream);
                        String sharedStringsCompmare;
                        String findStyles;

                        findStyles = @"xl/styles.xml";
                        String findSheetsName = @"xl/workbook.xml";
                        sharedStringsCompmare = @"xl/sharedStrings.xml";
                        String filenamefind = @"xl/worksheets/";

                        if (filenameCompare.Contains(filenamefind) && filename.Contains("rels") == false)
                        {
                            streams.Add(stream);
                        }
                        if (filenameCompare == sharedStringsCompmare)
                        {
                            sharedStringsStream = stream;
                        }
                        if (filenameCompare == findStyles)
                        {
                            getStyles = stream;
                        }
                        if (filenameCompare == findSheetsName)
                        {
                            Wookbooknames = stream;
                        }
                    }

                    stringTable = ExcelClass.ReadStringTable(sharedStringsStream);
                    listSheetNames = ExcelClass.getWorkbookNames(Wookbooknames, streams.Count());
                    sharedStringsStream.Close();
                }

                FileNames.ItemsSource = listSheetNames;
                FileNames.Visibility = Visibility.Visible;
            }
            catch (IOException)
            {
                MessageBox.Show("File is in use - Please close the file and try again!");
            }
        }

        public IEnumerable<IDictionary> GenerateData(IEnumerable<List<string>> data, IEnumerable<List<string>> Headers)
        {
            var key = headers.First();
            var values = headers.Last();
            //加载标题
            if (loadingHeaders == false)
            {
                TheGrid.Columns.Clear();
                for (int i = 0; i < key.Count(); i++)
                {
                    this.TheGrid.Columns.Add(
                    new DataGridTextColumn
                    {
                        Header = values[i],
                        Binding = new Binding(key[i])
                    });
                    loadingHeaders = true;
                }
            }

            foreach (List<string> row in data)
            {
                var dict = new Dictionary<string, object>();
                for (int j = 0; j < key.Count(); j++)
                {
                    dict[key[j]] = row[j];
                }
                yield return dict;
            }
        }

        private void FileNames_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            ConfirmButton.Visibility = Visibility.Visible;
        }

        /// <summary>
        /// 打开EXCEL
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ConfirmButton_Click(object sender, RoutedEventArgs e)
        {
            returnedlist = null;
            current = FileNames.SelectedItem.ToString();
            object[] returnlists = new object[2];
            int j = listSheetNames.Count() - 1;

            for (int i = 0; i < listSheetNames.Count(); i++)
            {
                if (listSheetNames[i] == current)
                {
                    returnlists = ExcelClass.ReadWorksheet(streams[j], stringTable, getStyles, getRowID);
                }
                else
                {
                    j--;
                }
            }
            returnedlist = (List<List<string>>)returnlists[0];
            headers = (List<List<string>>)returnlists[1];
            if (returnedlist.Count() == 0)
            {
                MessageBox.Show("Empty Workbook please select another");
            }
            else
            {
                TheGrid.ItemsSource = GenerateData(returnedlist, headers).ToDataSource();
                TheGrid.Visibility = Visibility.Visible;
                ConfirmButton.Visibility = Visibility.Collapsed;
                FileNames.Visibility = Visibility.Collapsed;
            }
        }     
    }

 

相关类

UnZipper.cs

public class UnZipper
    {
        private Stream stream;

        public UnZipper(Stream zipFileStream)
        {
            this.stream = zipFileStream;
        }

    
        public Stream GetFileStream(string filename)
        {
            Uri fileUri = new Uri(filename, UriKind.Relative);
            StreamResourceInfo info = new StreamResourceInfo(this.stream, null);
            if (this.stream is System.IO.FileStream) 
                this.stream.Seek(0, SeekOrigin.Begin);
            StreamResourceInfo stream = System.Windows.Application.GetResourceStream(info, fileUri);
            if (stream != null)
                return stream.Stream;
            return null;           
        }

       
        public IEnumerable<string> GetFileNamesInZip()
        {
            BinaryReader reader = new BinaryReader(stream);
            stream.Seek(0, SeekOrigin.Begin);
            string name = null;
            List<string> names = new List<string>();
            while (ParseFileHeader(reader, out name))
            {
                names.Add(name);
            }
            return names;
        }

    
        private static bool ParseFileHeader(BinaryReader reader, out string filename)
        {
            filename = null;
            if (reader.BaseStream.Position < reader.BaseStream.Length)
            {
                int headerSignature = reader.ReadInt32();
                if (headerSignature == 67324752) //ggggggrrrrrrrrrrrrrrrrr
                {
                    reader.BaseStream.Seek(2, SeekOrigin.Current); 
                   
                    short genPurposeFlag = reader.ReadInt16();
                    if (((((int)genPurposeFlag) & 0x08) != 0)) 
                        return false;
                    reader.BaseStream.Seek(10, SeekOrigin.Current); 
                    //short method = reader.ReadInt16(); //Unused
                    //short lastModTime = reader.ReadInt16(); //Unused
                    //short lastModDate = reader.ReadInt16(); //Unused
                    //int crc32 = reader.ReadInt32(); //Unused
                    int compressedSize = reader.ReadInt32();
                    int unCompressedSize = reader.ReadInt32();
                    short fileNameLenght = reader.ReadInt16();
                    short extraFieldLenght = reader.ReadInt16();
                    filename = new string(reader.ReadChars(fileNameLenght));
                    if (string.IsNullOrEmpty(filename))
                        return false;
                    
                    reader.BaseStream.Seek(extraFieldLenght + compressedSize, SeekOrigin.Current);
                    if (unCompressedSize == 0) 
                        return ParseFileHeader(reader, out filename);
                    else
                        return true;
                }
            }
            return false;
        }
    }

 DataSourceCreator.cs

public static class DataSourceCreator
    {
        private static readonly Regex PropertNameRegex =
                new Regex(@"^[A-Za-z]+[A-Za-z1-9_]*$", RegexOptions.Singleline);

        public static IEnumerable ToDataSource(this IEnumerable<IDictionary> list)
        {
            IDictionary firstDict = null;
            bool hasData = false;
            foreach (IDictionary currentDict in list)
            {
                hasData = true;
                firstDict = currentDict;
                break;
            }
            if (!hasData)
            {
                return new object[] { };
            }
            if (firstDict == null)
            {
                throw new ArgumentException("IDictionary entry cannot be null");
            }

            Type objectType = null;

            TypeBuilder tb = GetTypeBuilder(list.GetHashCode());

            ConstructorBuilder constructor =
                        tb.DefineDefaultConstructor(
                                    MethodAttributes.Public |
                                    MethodAttributes.SpecialName |
                                    MethodAttributes.RTSpecialName);

            foreach (DictionaryEntry pair in firstDict)
            {
                //if (PropertNameRegex.IsMatch(Convert.ToString(pair.Key), 0))
               // {
                    CreateProperty(tb,
                                    Convert.ToString(pair.Key),
                                    pair.Value == null ?
                                                typeof(object) :
                                                pair.Value.GetType());
               /* }
                else
                {
                    throw new ArgumentException()    ;
                   
                   
                }*/
            }
            objectType = tb.CreateType();

            return GenerateEnumerable(objectType, list, firstDict);
        }

        private static IEnumerable GenerateEnumerable(
                 Type objectType, IEnumerable<IDictionary> list, IDictionary firstDict)
        {
            var listType = typeof(List<>).MakeGenericType(new[] { objectType });
            var listOfCustom = Activator.CreateInstance(listType);

            foreach (var currentDict in list)
            {
                if (currentDict == null)
                {
                    throw new ArgumentException("IDictionary entry cannot be null");
                }
                var row = Activator.CreateInstance(objectType);
                foreach (DictionaryEntry pair in firstDict)
                {
                    if (currentDict.Contains(pair.Key))
                    {
                        PropertyInfo property =
                            objectType.GetProperty(Convert.ToString(pair.Key));
                        property.SetValue(
                            row,
                            Convert.ChangeType(
                                    currentDict[pair.Key],
                                    property.PropertyType,
                                    null),
                            null);
                    }
                }
                listType.GetMethod("Add").Invoke(listOfCustom, new[] { row });
            }
            return listOfCustom as IEnumerable;
        }

        private static TypeBuilder GetTypeBuilder(int code)
        {
            AssemblyName an = new AssemblyName("TempAssembly" + code);
            AssemblyBuilder assemblyBuilder =
                AppDomain.CurrentDomain.DefineDynamicAssembly(
                    an, AssemblyBuilderAccess.Run);
            ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule("MainModule");

            TypeBuilder tb = moduleBuilder.DefineType("TempType" + code
                                , TypeAttributes.Public |
                                TypeAttributes.Class |
                                TypeAttributes.AutoClass |
                                TypeAttributes.AnsiClass |
                                TypeAttributes.BeforeFieldInit |
                                TypeAttributes.AutoLayout
                                , typeof(object));
            return tb;
        }

        private static void CreateProperty(
                        TypeBuilder tb, string propertyName, Type propertyType)
        {
            FieldBuilder fieldBuilder = tb.DefineField("_" + propertyName,
                                                        propertyType,
                                                        FieldAttributes.Private);


            PropertyBuilder propertyBuilder =
                tb.DefineProperty(
                    propertyName, PropertyAttributes.HasDefault, propertyType, null);
            MethodBuilder getPropMthdBldr =
                tb.DefineMethod("get_" + propertyName,
                    MethodAttributes.Public |
                    MethodAttributes.SpecialName |
                    MethodAttributes.HideBySig,
                    propertyType, Type.EmptyTypes);

            ILGenerator getIL = getPropMthdBldr.GetILGenerator();

            getIL.Emit(OpCodes.Ldarg_0);
            getIL.Emit(OpCodes.Ldfld, fieldBuilder);
            getIL.Emit(OpCodes.Ret);

            MethodBuilder setPropMthdBldr =
                tb.DefineMethod("set_" + propertyName,
                  MethodAttributes.Public |
                  MethodAttributes.SpecialName |
                  MethodAttributes.HideBySig,
                  null, new Type[] { propertyType });

            ILGenerator setIL = setPropMthdBldr.GetILGenerator();

            setIL.Emit(OpCodes.Ldarg_0);
            setIL.Emit(OpCodes.Ldarg_1);
            setIL.Emit(OpCodes.Stfld, fieldBuilder);
            setIL.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getPropMthdBldr);
            propertyBuilder.SetSetMethod(setPropMthdBldr);
        }
    }

ExcelClass.cs

public class ExcelClass
    {
        public static List<string> ReadStringTable(Stream input)
        {
            List<string> stringTable = new List<string>();
            using (XmlReader reader = XmlReader.Create(input))
            {
                for (reader.MoveToContent(); reader.Read(); )
                    if (reader.NodeType == XmlNodeType.Element && reader.Name == "t")
                        stringTable.Add(reader.ReadElementContentAsString());
            }
            return stringTable;
        }

        public static List<string> getWorkbookNames(Stream workbook, int count)
        {
            List<string> workBookTable = new List<string>();
            int Count = count;

            using (XmlReader reader = XmlReader.Create(workbook))
            {
                for (reader.MoveToContent(); reader.Read(); )
                    if (reader.NodeType == XmlNodeType.Element && reader.Name == "sheets")
                    {
                        reader.Read();

                        if (reader.Name == "sheet")
                        {
                            for (int i = 0; i < Count; i++)
                            {
                                workBookTable.Add(reader.GetAttribute("name"));
                                reader.Read();
                            }
                        }
                    }
            }
            return workBookTable;
        }

        public static object[] ReadWorksheet(Stream input, List<string> stringTable, Stream style, int rowID)
        {
            List<List<string>> workbook = new List<List<string>>();
            List<List<string>> headers = new List<List<string>>();
            List<string> cHeader = new List<string>();
            List<string> cValue = new List<string>();
            List<string> row = new List<string>();

            Stream StyleStream = style;
            String type;
            String STRFinder;
            Int32 StyleIndex = 0;
            Int32 StyleInt = 0;
            String nullFinder;
            Int32 val = -1;
            String firstLook = null;
            int headerCount = 0;
            bool runRow = false;
            List<string> styleValues = new List<string>();
            int getRowID = rowID;

            string newstr = string.Empty;
            object[] objects = new object[2];

            if (getRowID == 0)
            {
                getRowID = 1;
            }
            XmlReader Stylereader = Stylereader = XmlReader.Create(StyleStream);
            Stylereader.MoveToContent();

            while (Stylereader.Read())
            {
                if (Stylereader.NodeType == XmlNodeType.Element)
                {
                    switch (Stylereader.Name)
                    {
                        case "cellXfs":

                           Int16 Count = Convert.ToInt16(Stylereader.GetAttribute("count"));

                            Stylereader.Read();

                            for (int i = 0; i < Count; i++)
                            {
                                if (Stylereader.Name == "xf")
                                {
                                    styleValues.Add(Stylereader.GetAttribute("numFmtId"));
                                    Stylereader.Skip();
                                }
                            }
                            break;
                    }
                }
            }


            using (XmlReader reader = XmlReader.Create(input))
            {
                int currentPostion = 0;
                int postionFound = 0;

                for (reader.MoveToContent(); reader.Read(); )

                    if (reader.NodeType == XmlNodeType.Element)
                    {
                        switch (reader.Name)
                        {
                            case "row":

                                int getRowVale = Convert.ToInt32(reader.GetAttribute("r"));

                                if (getRowVale >= getRowID)
                                {
                                    runRow = true;
                                }
                                else
                                {
                                    runRow = false;

                                }
                                break;

                            case "c":

                                if (runRow == true)
                                {

                                    type = reader.GetAttribute("t");
                                    nullFinder = reader.GetAttribute("r");
                                    if (nullFinder == "K3")
                                    {

                                    }
                                    StyleIndex = Convert.ToInt16(reader.GetAttribute("s"));
                                    STRFinder = reader.GetAttribute("t");

                                    if (StyleIndex > 0)
                                    {

                                        StyleInt = Convert.ToInt32(styleValues[StyleIndex]);
                                    }
                                    newstr = string.Empty;


                                    postionFound = 0;

                                    if (nullFinder != null)
                                    {
                                        foreach (char c in nullFinder)
                                        {
                                            try
                                            {
                                                int i = (int)c;
                                                if ((i >= 48) && (i <= 57))
                                                {
                                                    continue;
                                                }
                                            }
                                            catch (Exception)
                                            {

                                            }

                                            newstr += c.ToString();
                                        }
                                        foreach (char c in newstr)
                                        {
                                            postionFound += ((int)c);
                                        }

                                        if (newstr.Length == 1)
                                        {
                                            postionFound = postionFound - 65;
                                        }
                                        else if (newstr.Length == 2)
                                        {
                                            postionFound = postionFound - 104;
                                        }
                                        else if (newstr.Length == 3)
                                        {
                                            postionFound = postionFound - 119;
                                        }

                                        if (currentPostion != postionFound)
                                        {
                                            for (int i = currentPostion; i < postionFound; i++)
                                            {
                                                row.Add(Convert.ToString(null));
                                            }
                                            currentPostion = postionFound + 1;
                                        }
                                        else
                                        {
                                            currentPostion = postionFound + 1;
                                        }
                                    }

                                    try
                                    {
                                        //skips the formula element to the value
                                        
                                        //only allows "c" with elements skips empty elemets
                                        if ((bool)reader.IsEmptyElement == false)
                                        {
                                            reader.Read();
                                          
                                            if (reader.Name.Equals("f"))
                                            {
                                                reader.Skip();
                                                firstLook = reader.ReadElementContentAsString().ToString();
                                                row.Add(firstLook);
                                                break;
                                            }
                                            else
                                            {
                                                firstLook = reader.ReadElementContentAsString().ToString();
                                                if (firstLook.Contains("."))
                                                {
                                                    row.Add(firstLook);
                                                    break;
                                                }                                          
                                                else
                                                {                                                                                                 
                                                    val = int.Parse(firstLook);
                                                }

                                            }                                         
                                        }
                                        else
                                        {
                                            break;
                                        }
                                    }
                                    catch (FormatException)
                                    {
                                        MessageBox.Show("invaild data type " + reader.ReadElementContentAsString());
                                    }
                                }
                                else
                                {
                                    break;
                                }


                                if (type == "s")
                                {
                                    if (headerCount == 0)
                                    {
                                        cHeader.Add(newstr);
                                        cValue.Add(stringTable[val]);
                                        newstr = string.Empty;
                                    }
                                    else
                                    {                                 
                                        row.Add(stringTable[val]);
                                    }

                                    break;
                                }

                                else if (StyleInt > 13 && StyleInt < 23)
                                {
                                    DateTime dt = DateTime.FromOADate(val);
                                    String DateandTime = Convert.ToString(dt.ToShortDateString());
                                    row.Add(DateandTime);
                                    StyleInt = 0;
                                    StyleIndex = 0;

                                }
                                //else if (STRFinder == "str")
                                //{
                                //    //string readSTR = reader.ReadElementContentAsString().ToString();
                                //    row.Add(Convert.ToString(readSTR));
                                //    break;
                                //}
                                else
                                {
                                    row.Add(Convert.ToString(val));
                                }
                                break;
                        }

                    }
                    else if (reader.NodeType == XmlNodeType.EndElement)
                        if (reader.Name == "row")
                        {
                            if (headerCount == 0 && cValue.Count != 0)
                            {
                                headers.Add(cHeader);
                                headers.Add(cValue);
                                headerCount += 1;
                                currentPostion = 0;
                                postionFound = 0;
                            }
                            else if (row.Count != 0)
                            {
                                if (row.Count != cHeader.Count)
                                {
                                    for (int i = row.Count; i < cHeader.Count; i++)
                                    {
                                        row.Add(Convert.ToString(null));
                                    }
                                }

                                List<string> newRow = new List<string>();
                                foreach (string item in row)

                                    newRow.Add(item);
                                workbook.Add(newRow);

                                currentPostion = 0;
                                postionFound = 0;
                                row.Clear();
                            }
                        }

                objects[0] = workbook;
                objects[1] = headers;
                return objects;
            }
        }
    }