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(); } } } }