你的位置:首页 > ASP.net教程

[ASP.net教程]csharp: Importing or Exporting Data from Worksheets using aspose cell


/// <summary>  /// 涂聚文  /// 20150728  /// EXCEL  /// </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