csharp: read excel using Aspose.Cells，csharpaspose.cells
/// <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(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);
}
}
/// <summa
/// <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;
//
// ("[email protected]", "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;
}
/// <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;
}
