////// /// /// ///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); } /// /// /// /// /// ///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); } /// /// 读取工作表 /// 涂聚文 /// 20150228 /// /// /// 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(book.Worksheets[i].Name.ToString()); } // Cells cells = sheet.Cells; //return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true); } ////// 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 /// /// /// /// ///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; } } /// /// 工作表转为图片 /// /// 来源EXCEL文件 /// 工作表名 /// 生成图片文件 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); } ////// /// /// /// 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); } } ////// 涂聚文 /// 20150228 /// /// /// 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.Cursor = Cursors.Default; } else { MessageBox.Show("错添文件类型"); } } else { MessageBox.Show("你要选择一下精确位置的文件"); } } } catch (Exception ex) { ex.Message.ToString(); } this.Cursor = Cursors.Default; } ////// 导入 /// /// /// 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; }
////// /// public partial class _Default : System.Web.UI.Page { DataTable getData() { DataTable dt = new DataTable(); dt.Columns.Add("id", typeof(int)); dt.Columns.Add("name", typeof(string)); dt.Rows.Add(1, "geovindu"); dt.Rows.Add(2, "geov"); return dt; } ////// /// /// /// protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { this.GridView1.DataSource = getData(); this.GridView1.DataBind(); } } ////// /// /// /// protected 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(); } protected void Button1_Click(object sender, EventArgs e) { ExportToExcel(getData(), "toni"); }