csharp: read excel using Aspose.Cells

来源：互联网发布：懒人之家js特效怎么用编辑：程序博客网时间：2024/06/04 23:29

    /// <summary>        ///         /// </summary>        /// <param name="strFileName"></param>        /// <returns></returns>        public static System.Data.DataTable ReadExcel(String strFileName)        {            Workbook book = new Workbook(strFileName);            //book.Open(strFileName); //老版本            Worksheet sheet = book.Worksheets[0];                        Cells cells = sheet.Cells;            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);        }        /// <summary>        ///         /// </summary>        /// <param name="strFileName"></param>        /// <param name="sheetname"></param>        /// <returns></returns>        public static System.Data.DataTable ReadExcel(String strFileName,string sheetname)        {            Workbook book = new Workbook(strFileName);            //book.Open(strFileName);//老版本            Worksheet sheet = book.Worksheets[sheetname];            Cells cells = sheet.Cells;            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);        }        /// <summary>        /// 读取工作表        /// 涂聚文        /// 20150228        /// </summary>        /// <param name="strFileName"></param>        /// <param name="comb"></param>        public static void ReadExcelCombox(String strFileName, System.Windows.Forms.ComboBox comb)        {            comb.Items.Clear();            Workbook book = new Workbook(strFileName);            // book.Open(strFileName);//老版本            Worksheet sheet = book.Worksheets[0];            for (int i = 0; i < book.Worksheets.Count; i++)            {                comb.Items.Add(new ItemProvince(i,book.Worksheets[i].Name.ToString()));              }           // Cells cells = sheet.Cells;            //return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);        }        /// <summary>        /// DataTable导出到EXCEL        /// http://www.aspose.com/docs/display/cellsnet/Aspose.Cells+Object+Model        /// http://www.aspose.com/docs/display/cellsnet/Converting+Worksheet+to+Image+and+Worksheet+to+Image+by+Page        /// </summary>        /// <param name="datatable"></param>        /// <param name="filepath"></param>        /// <param name="error"></param>        /// <returns></returns>        public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)        {            error = "";            try            {                if (datatable == null)                {                    error = "DataTableToExcel:datatable 为空";                    return false;                }                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();                Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];                Aspose.Cells.Cells cells = sheet.Cells;                int nRow = 0;                foreach (DataRow row in datatable.Rows)                {                    nRow++;                    try                    {                        for (int i = 0; i < datatable.Columns.Count; i++)                        {                            if (row[i].GetType().ToString() == "System.Drawing.Bitmap")                            {                                //------插入图片数据-------                                System.Drawing.Image image = (System.Drawing.Image)row[i];                                MemoryStream mstream = new MemoryStream();                                image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);                                sheet.Pictures.Add(nRow, i, mstream);                            }                            else                            {                                cells[nRow, i].PutValue(row[i]);                            }                        }                    }                    catch (System.Exception e)                    {                        error = error + " DataTableToExcel: " + e.Message;                    }                }                workbook.Save(filepath);                return true;            }            catch (System.Exception e)            {                error = error + " DataTableToExcel: " + e.Message;                return false;            }        }        /// <summary>        /// 工作表转为图片        /// </summary>        /// <param name="file">来源EXCEL文件</param>        /// <param name="sheetname">工作表名</param>        /// <param name="toimagefile">生成图片文件</param>        public static void CellConverImge(string file, string sheetname, string toimagefile)        {            //Create a new Workbook object and            //Open a template Excel file.            Workbook book = new Workbook(file);            //Get the first worksheet.            Worksheet sheet = book.Worksheets[sheetname];            //Define ImageOrPrintOptions            ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();            //Specify the image format            imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;            //Only one page for the whole sheet would be rendered            imgOptions.OnePagePerSheet = true;            //Render the sheet with respect to specified image/print options            SheetRender sr = new SheetRender(sheet, imgOptions);            //Render the image for the sheet            Bitmap bitmap = sr.ToImage(0);            //Save the image file specifying its image format.            bitmap.Save(toimagefile);        }        /// <summary>        ///         /// </summary>        /// <param name="sURL"></param>        /// <param name="toExcelFile"></param>        public static void LoadUrlImage(string sURL,string toExcelFile)        {            //Define memory stream object            System.IO.MemoryStream objImage;            //Define web client object            System.Net.WebClient objwebClient;            //Define a string which will hold the web image url            //string sURL = "http://files.myopera.com/Mickeyjoe_irl/albums/38458/abc.jpg";            try            {                //Instantiate the web client object                objwebClient = new System.Net.WebClient();                //Now, extract data into memory stream downloading the image data into the array of bytes                objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL));                //Create a new workbook                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();                //Get the first worksheet in the book                Aspose.Cells.Worksheet sheet = wb.Worksheets[0];                //Get the first worksheet pictures collection                Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures;                //Insert the picture from the stream to B2 cell                pictures.Add(1, 1, objImage);                //Save the excel file  "d:\\test\\webimagebook.xls"                wb.Save(toExcelFile);            }            catch (Exception ex)            {                //Write the error message on the console                Console.WriteLine(ex.Message);            }        }

  /// <summary>        /// 涂聚文        /// 20150228        /// </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.FileName = "";                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;                            //                             // ("463588883@qq.com", "geovindu", "金至尊文件", "文件", currentfilename);                            //MessageBox.Show(imail.ToString());                            AsposeExcel.ReadExcelCombox(currentfilename,combSheet);                            this.combSheet.SelectedIndex = 0;                                 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)        {            DataTable dt = new DataTable();             //默认第一行为标题             dt= AsposeExcel.ReadExcel(this.txtFileUrl.Text.Trim(), this.combSheet.Text.Trim());             this.dataGridView1.DataSource = dt;                   }

0 0