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

[ASP.net教程]csharp: Export or Import excel using MyXls


excel 2003 (效果不太理想)

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using org.in2bits.MyXls;using org.in2bits.MyXls.ByteUtil;using System.IO;using Directory = org.in2bits.MyOle2.Directory;using NUnit.Framework;using org.in2bits.MyOle2;using System.Diagnostics;namespace MyxlsDemo{  /// <summary>  /// 涂聚文  /// 20150730  /// 效果不太理想.  /// </summary>  public partial class Form2 : Form  {    string strFileUrl = "";    /// <summary>    ///     /// </summary>    /// <returns></returns>    DataSet setData()    {      //Create an Emplyee DataTable      DataTable employeeTable = new DataTable("Employee");      employeeTable.Columns.Add("Employee ID");      employeeTable.Columns.Add("Employee Name");      employeeTable.Rows.Add("1", "涂聚文");      employeeTable.Rows.Add("2", "geovindu");      employeeTable.Rows.Add("3", "李蘢怡");      employeeTable.Rows.Add("4", "ноппчц");      employeeTable.Rows.Add("5", "ニヌネハヒフキカォноппчц");      //Create a Department Table      DataTable departmentTable = new DataTable("Department");      departmentTable.Columns.Add("Department ID");      departmentTable.Columns.Add("Department Name");      departmentTable.Rows.Add("1", "IT");      departmentTable.Rows.Add("2", "HR");      departmentTable.Rows.Add("3", "Finance");      //Create a DataSet with the existing DataTables      DataSet ds = new DataSet("Organization");      ds.Tables.Add(employeeTable);      ds.Tables.Add(departmentTable);      return ds;    }    /// <summary>    ///     /// </summary>    public Form2()    {      InitializeComponent();    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void Form2_Load(object sender, EventArgs e)    {      this.dataGridView1.DataSource = setData().Tables[0];    }    /// <summary>    /// Excel 2003    /// 涂聚文    /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void btnFile_Click(object sender, EventArgs e)    {      try      {        //bool imail = false;        this.Cursor = Cursors.WaitCursor;        openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);        //JPEG Files (*.jpeg)|*.jpeg|PNG Files (*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif        openFileDialog1.Filter = "Excel 2000-2003 files(*.xls)|*.xls|Excel 2007 files (*.xlsx)|*.xlsx";//|(*.xlsx)|*.xlsx Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.* txt files (*.txt)|*.txt|All files (*.*)|*.*"         openFileDialog1.FilterIndex = 2;        openFileDialog1.RestoreDirectory = true;        if (openFileDialog1.ShowDialog() == DialogResult.OK)        {          if (!openFileDialog1.FileName.Equals(String.Empty))          {            //重新加载清除数据            //this.combSheet.DataSource = null;            //if (this.combSheet.Items.Count != 0)            //{            //  this.combSheet.Items.Clear();            //}            FileInfo f = new FileInfo(openFileDialog1.FileName);            if (f.Extension.Equals(".xls") || f.Extension.Equals(".XLS") || f.Extension.Equals(".xlsx"))            {              this.Cursor = Cursors.WaitCursor;              strFileUrl = openFileDialog1.SafeFileName;              this.txtFileUrl.Text = openFileDialog1.FileName;              string currentfilename = openFileDialog1.FileName;              this.txtFileUrl.Text = currentfilename;              XlsDocument xls = new XlsDocument(currentfilename);              DataTable com = new DataTable();              com.Columns.Add("id", typeof(int));              com.Columns.Add("name", typeof(string));              // xls.FileName = currentfilename;              for(int id = 0; id < xls.Workbook.Worksheets.Count; id++)              {                com.Rows.Add(id,xls.Workbook.Worksheets[id].Name);              }              this.combSheet.DataSource = com;              this.combSheet.DisplayMember = "name";              this.combSheet.ValueMember = "id";              Worksheet sheet = xls.Workbook.Worksheets[0];              DataTable dt = new DataTable();              //xls.Workbook.Worksheets[0].Name.ToString();              int i = 0;              int FirstRow = (int)sheet.Rows.MinRow;              if (i == 0)              {                //write data in every cell in the first row in the first worksheet as the column header(note: in order to write data from xls document in DataTable)                for (int j = 1; j < sheet.Rows[1].CellCount + 1; j++)                {                  string ColumnName = Convert.ToString(sheet.Rows[1].GetCell(ushort.Parse(j.ToString())).Value);                  DataColumn column = new DataColumn(ColumnName);                  dt.Columns.Add(column);                }                FirstRow++;              }              // write data(not including column header) in datatable rows in sequence              for (int k = FirstRow; k < sheet.Rows.MaxRow + 1; k++)              {                Row row = sheet.Rows[ushort.Parse(k.ToString())];                DataRow dataRow = dt.NewRow();                for (int z = 1; z < sheet.Rows[ushort.Parse(k.ToString())].CellCount + 1; z++)                {                  // write data in the current cell if it exists                  if (row.GetCell(ushort.Parse(z.ToString())) != null)                  {                    dataRow[z - 1] = row.GetCell(ushort.Parse(z.ToString())).Value.ToString();                  }                }                dt.Rows.Add(dataRow);              }              this.dataGridView1.DataSource = dt;              this.Cursor = Cursors.Default;            }            else            {              MessageBox.Show("错添文件类型");            }          }          else          {            MessageBox.Show("你要选择一下精确位置的文件");          }        }      }      catch (Exception ex)      {        ex.Message.ToString();      }            this.Cursor = Cursors.Default;    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void btnImport_Click(object sender, EventArgs e)    {    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void buttonExport_Click(object sender, EventArgs e)    {      ExportEasy(setData().Tables[0], "ex.xls");    }    /// <summary>    /// 导出    /// </summary>    /// <param name="dtSource"></param>    /// <param name="strFileName"></param>    public static void ExportEasy(DataTable dtSource, string strFileName)    {      try      {        XlsDocument xls = new XlsDocument();        Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");        //填充表头        foreach (DataColumn col in dtSource.Columns)        {          sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);        }        //填充内容        for (int i = 0; i < dtSource.Rows.Count; i++)        {          for (int j = 0; j < dtSource.Columns.Count; j++)          {            sheet.Cells.Add(i + 2, j + 1, dtSource.Rows[i][j].ToString());          }        }        //保存        xls.FileName = strFileName;        xls.Save();      }      catch (Exception ex)      {        ex.Message.ToString();      }    }  }}