用C#语言实现读取修改保存EXCEL表格

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using org.in2bits.MyXls;

using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Data.SqlClient;

namespace ExcelExportTest
{
    class Program
    {
        public static string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =D:\\000.xls;Extended Properties=Excel 8.0";
        public static DataSet ds;
        public static int i = 1, j, m, n = 0,a=0,r=0;
        public static int t=0,x=0,y=0,z=0,q=0,w=0;
        public static string ss, ss1;
        public static string[] arry = new string[2000];
        public static string[] arrys = new string[2000];
        public static Int32[] arryh = new Int32[2000];
        public static string[,] arry1 = new string[100,2000];
        public static string[,] arry2 = new string[100, 2000];
          static void Main(string[] args)
        {           
            excel_show();
            Export();
        }
        static void excel_show()
        {
            OleDbConnection conn = new OleDbConnection(strCon);
            conn.Open();
            ds = new DataSet();
            string sql2 = "select * from [Sheet1$]";
            OleDbDataAdapter myCommand2 = new OleDbDataAdapter(sql2, strCon);
            myCommand2.Fill(ds, "[Sheet1$]");
            for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                for (j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    ss = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                    if (ss.Contains("SOE"))
                    {
                        m = ss.Length;
                        ss1 = ss.Substring(m - 30);
                        ss = ss.Remove(m - 30);
                        ss = ss.Remove(6, 20);
                        ss = ss.Insert(6, ss1);
                        arry[n++] = ss;
                        //Console.WriteLine(ss);
                    }
                }
            for (i = 0; i<n;i++ )
            {
                  arrys[i]="---";
            }   
                for (i = 0; i < n; i++)
                  {
                        ss1 = arry[i].Substring(40, 17);
                        j = 0;                       
                        r = 0;
                        do
                        {
                            ss = arrys[j];
                            if (ss1.Trim() == ss.Trim())
                            {
                                r++;
                                break;
                            }
                            j++;
                        } while (j < a);
                        if (r == 0)
                        {
                            arrys[a++] = ss1.Trim();
                        }
                    }           
            Console.WriteLine("总共有{0}中不同的种类", a);
            for (i = 0; i < a; i++)
                for (j = 0; j < n; j++)
                {
                    arry1[i, j] = "---";
                    arry2[i, j] = "---";
                    arryh[j] = 0;

                }
            for (j = 0; j < a; j++)
                Console.WriteLine("--{0}",arrys[j]);
            x = 0;
           for (j = 0; j < n; j++)
            {

               for(i=0;i<a;i++)
               {
                  if(arry[j].Contains(arrys[i]))
                      arry1[i,x++] = arry[j];
               }       
           
            }
           Console.WriteLine("i={0},x={1},n={2}",i,x,n);

           for (i = 0; i < a; i++)
           {
               y = 0;
               for(j=0;j<n;j++)
               {
                   if (arry1[i,j].Contains("SOE"))
                       arry2[i,y++] = arry1[i, j];
               }          
           }
           for (i = 0;i<a ;i++ )
           {
               for (j = 0; j<n;j++ )
               {
                   if (arry2[i, j].Contains("SOE"))
                       arryh[i]+=1;
                   //Console.WriteLine("{0}", arry2[i,j]);
               }
           }
           j = 0;
           for (i = 0; i < a; i++)
           {
               j += arryh[i];
               Console.WriteLine("每{0}维的数组长度为：{1}",i, arryh[i]);
           }
            if(j==n)
           Console.WriteLine("每一维的长度加起来等于总和，正确！总的长度为{0}",j);
            x = 0;
           for (i = 0; i < a; i++)
           {
               for (j = 0; j < arryh[i]; j++)
               {
                 arry[x++]=arry2[i,j];
               }
           }
                                                            conn.Close();
        }
        static void Export()
        {
            ColorT colort = new ColorT();
            Dictionary<string, Color> list = colort.GetColorList();
            XlsDocument document = new XlsDocument();         
            Worksheet sheet = document.Workbook.Worksheets.Add("detail");
            sheet.Name = "detail";

            XF myxf = document.NewXF();
            myxf.UseBackground = true;
            myxf.Pattern = 1;

            Cells cell = sheet.Cells;
            int num = 1;
            int row = 1;
            int column = 1;           
            foreach (string key in list.Keys)
            {
                myxf.PatternColor = list[key];
                myxf.PatternBackgroundColor = list[key];

                cell.Add(row, column, key, myxf);               
                    row++;
                    column = 0;       

                    num++;
                    column++;
             
            }
            document.FileName = "testexcel.xls";
            document.Save(true);

            System.Diagnostics.Process.Start("testexcel.xls");
        }
    }
    public class ColorT
    {
        private Dictionary<string, Color> list = new Dictionary<string, Color>();
        public ColorT()
        {

        }      
        public Dictionary<string, Color> GetColorList()
        {  
            int i,t=0;           
            t = Program.n;
            string[] arry = new string[t];         
            Console.WriteLine("count={0}",t);
            for (i = 0; i < t; i++)
            {
                arry[i] = Program.arry[i];
              }
            for(i=0;i<t;i++)
            list.Add(arry[i], SystemWindowBackgroundColorForPatternBackground);           
            return list;
        }       
        public static readonly Color SystemWindowBackgroundColorForPatternBackground = Colors.SystemWindowBackgroundColorForPatternBackground;
       
    }
}