csharp: Importing or Exporting Data from Worksheets using aspose cell，csharpimporting

csharp: Importing or Exporting Data from Worksheets using aspose cell，csharpimporting

/// <summary>
    /// 塗聚文
    /// 20150728
    /// EXCEL win7 32位,64位OK
    /// </summary>
    public class ExcelHelperImport
    {

        /*
 http://www.cnblogs.com/wangrsh2010/archive/2012/03/21/2410182.html
 * http://npoi.codeplex.com/SourceControl/latest
 * http://sourceforge.net/projects/myxls/
http://svn.code.sf.net/p/myxls/code/trunk myxls-code
 */

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strFileName"></param>
        /// <param name="inumber"></param>
        /// <returns></returns>
        public static System.Data.DataTable ReadExcel(String strFileName,int inumber)
        {
            Workbook book = new Workbook();
            book.Open(strFileName); //過時
            Worksheet sheet = book.Worksheets[inumber];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }
        /// <summary>
        /// geovindu 
        /// </summary>
        /// <param name="strFileName"></param>
        /// <param name="num"></param>
        /// <returns></returns>
        public static DataTable ImportExcel(string strFileName, int num)
        {
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(strFileName);

            ////Creating a file stream containing the Excel file to be opened
            //FileStream fstream = new FileStream(strFileName, FileMode.Open);
            ////Instantiating a Workbook object
            ////Opening the Excel file through the file stream
            //Workbook workbook = new Workbook(fstream);

            //Accessing the first worksheet in the Excel file
            Worksheet worksheet = workbook.Worksheets[num];
            Cells cells = worksheet.Cells;
            //Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
            //DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 7, 2, true);
            DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, cells.MaxDataRow+1 , cells.MaxDataColumn+1 , false);
            //fstream.Close();
            return dataTable;

        }

        /// <summary>
        /// geovindu 塗聚文
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="response"></param>
        private static void Export<T>(IEnumerable<T> data, HttpResponse response,string filename)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = (Worksheet)workbook.Worksheets[0];

            PropertyInfo[] ps = typeof(T).GetProperties();
            var colIndex = "A";

            foreach (var p in ps)
            {

                sheet.Cells[colIndex + 1].PutValue(p.Name);
                int i = 2;
                foreach (var d in data)
                {
                    sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
                    i++;
                }

                colIndex = ((char)(colIndex[0] + 1)).ToString();
            }

            response.Clear();
            response.Buffer = true;
            response.Charset = "utf-8";
            response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.ContentType = "application/ms-excel";
            response.BinaryWrite(workbook.SaveToStream().ToArray());
            response.End();
        }

        /// <summary>
        /// Geovin Du
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        public static void ExportToExcel(DataTable dataTable, string fileName)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            foreach (DataColumn column in dataTable.Columns)
            {
                context.Response.Write(column.ColumnName + ",");
            }
            context.Response.Write(Environment.NewLine);
 
            foreach (DataRow row in dataTable.Rows)
            {
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    context.Response.Write(row[i].ToString() + ",");
                }
                context.Response.Write(Environment.NewLine);
            }
            context.Response.ContentType = "application / ms - excel";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");
            context.Response.End();
        }
  
    }

　　from: http://www.aspose.com/.net/excel-component.aspx

https://github.com/heavenwing/WeiXinSDK

https://github.com/heavenwing/MyWeChatPublic

https://github.com/geffzhang/opendotnet

https://github.com/jrsoftware/issrc

http://sourceforge.net/projects/ibatisnet/files/ibatisnet/

http://sourceforge.net/projects/nhibernate/files/?source=navbar

http://sourceforge.net/projects/castleproject/files/?source=navbar

https://github.com/castleproject/

 /// <summary>
        /// 獲取工作表名稱
        /// </summary>
        /// <param name="strFileName"></param>
        /// <returns></returns>
        private  static DataTable getDataSheetName(string strFileName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("name", typeof(string));
           // Aspose.Cells.Workbook workbook = new Workbook();//4.0
            //workbook.Open(strFileName);//4.0
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(strFileName);// 7.0
            for(int i=0;i<workbook.Worksheets.Count;i++)
            {
                dt.Rows.Add(i, workbook.Worksheets[i].Name);
            }
            return dt;
        }

        /// <summary>
        /// 獲取工作表名稱
        /// </summary>
        /// <param name="strFileName"></param>
        /// <param name="com"></param>
        public static void getSheetName(String strFileName, System.Windows.Forms.ComboBox com)
        {
            DataTable dt = getDataSheetName(strFileName);
            com.DataSource = dt;
            com.DisplayMember = "name";
            com.ValueMember = "id";
            com.AutoCompleteMode = AutoCompleteMode.Suggest;
            com.AutoCompleteSource = AutoCompleteSource.ListItems;
            KillExcelProceed();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strFileName">文件</param>
        /// <param name="inumber">第幾個工作表</param>
        /// <returns></returns>
        public static System.Data.DataTable ReadExcel(String strFileName, int inumber)
        {
            Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(strFileName);// 7.0
            //Workbook book = new Workbook();
            //book.Open(strFileName); // 4.0 過時
            //book.Worksheets.Count;
            Worksheet sheet = book.Worksheets[inumber];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }