C#获取excel文件中单元格文字内容
/// <summary> /// 获取数据. /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <returns></returns> public static string GetValue(int row, int col) { // 取得单元格. var cell = xlSheet.Cells[row, col]; if (cell.MergeCells == true) { // 本单元格是 “合并单元格” if (cell.MergeArea.Row == row && cell.MergeArea.Column == col) { // 当前单元格 就是 合并单元格的 左上角 内容. return cell.Text; } else { // 返回 合并单元格的 左上角 内容. return xlSheet.Cells[cell.MergeArea.Row, cell.MergeArea.Column].Text; } } else { // 本单元格是 “普通单元格” // 获取文本信息. return cell.Text; } } //注:xlSheet 是 Excel 工作表对象. /// <summary> /// Excel 工作表. /// </summary> private static Microsoft.Office.Interop.Excel.Worksheet xlSheet =null;
获取图片信息如下:
控制台中使用剪贴板要注意,main函数 增加 [STAThread] 才能用哦。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.Office.Interop.Excel; using System.Reflection; using System.Runtime.InteropServices.ComTypes; using System.Windows.Forms; using System.Drawing; using Microsoft.Office.Core; using System.IO; using System.Drawing.Imaging; namespace testExcel { class Program { /// <summary> /// /// </summary> /// <param name="args"></param> [STAThread] static void Main(string[] args) { string filePath = @"F:\test.xls"; Microsoft.Office.Interop.Excel.Application xApp=new Microsoft.Office.Interop.Excel.Application(); xApp.Visible=false; xApp.UserControl = true; Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(filePath, Missing.Value,Missing.Value,Missing.Value,Missing.Value ,Missing.Value,Missing.Value,Missing.Value,Missing.Value ,Missing.Value,Missing.Value,Missing.Value,Missing.Value); xBook = xApp.Workbooks.Add(filePath); Console.WriteLine("一共"+xBook.Sheets.Count+"个Sheet"); //遍历sheet for(int j=0;j<xBook.Sheets.Count;j++) { xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[j+1]; Console.WriteLine("第"+j+"个sheet,名称"+xlSheet.Name); //读取数据 //遍历数据 int value = 5; for (int i = 1; i < 100; i = i + 7) { string title = GetValue(5, i); if (string.IsNullOrEmpty(title)==false) { //名称 Console.WriteLine(title); //得分 Console.WriteLine(GetValue(5, value)); //日期 Console.WriteLine(GetValue(6, i)); //时间 Console.WriteLine(GetValue(6, value - 1)); } value = value + 7; } ////图片个数 //int picCount=xlSheet.Shapes.Count; ////类型 //MsoShapeType msoShapeType= xlSheet.Shapes.Item(1).Type; ////图片对象名称 //string picName = xlSheet.Shapes.Item(1).Name; ////图片所在位置 ////行 //int picRows= xlSheet.Shapes.Item(1).TopLeftCell.Row; ////列 //int picC = xlSheet.Shapes.Item(1).TopLeftCell.Column; int StartRow = 2; Microsoft.Office.Interop.Excel.Worksheet sheet = xBook.Worksheets.get_Item(j + 1) as Microsoft.Office.Interop.Excel.Worksheet;//从1开始. for (int row = StartRow; row <= sheet.UsedRange.Rows.Count; row++) { //取存图片; if (sheet.Shapes.Count > row - StartRow) { Microsoft.Office.Interop.Excel.Shape s = sheet.Shapes.Item(row - StartRow + 1) as Microsoft.Office.Interop.Excel.Shape; //图片对象名称 string picNamed = sheet.Shapes.Item(row - StartRow + 1).Name; s.ScaleHeight(1f, MsoTriState.msoTrue, MsoScaleFrom.msoScaleFromTopLeft); s.ScaleWidth(1f, MsoTriState.msoTrue, MsoScaleFrom.msoScaleFromTopLeft); s.CopyPicture(Appearance.Button, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlBitmap); //COPY到内存。 System.Windows.Forms.IDataObject iData = Clipboard.GetDataObject(); if (iData.GetDataPresent(DataFormats.Bitmap)) { Image image = (Bitmap)iData.GetData(DataFormats.Bitmap); Bitmap bitmap = new Bitmap(image); bitmap.Save(string.Format(@"C:\{0}.jpg", picNamed)); } } } } xBook.Close(); Console.Read(); } /// <summary> /// 获取数据. /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <returns></returns> public static string GetValue(int row, int col) { // 取得单元格. var cell = xlSheet.Cells[row, col]; if (cell.MergeCells == true) { // 本单元格是 “合并单元格” if (cell.MergeArea.Row == row && cell.MergeArea.Column == col) { // 当前单元格 就是 合并单元格的 左上角 内容. return cell.Text; } else { // 返回 合并单元格的 左上角 内容. return xlSheet.Cells[cell.MergeArea.Row, cell.MergeArea.Column].Text; } } else { // 本单元格是 “普通单元格” // 获取文本信息. return cell.Text; } } //注:xlSheet 是 Excel 工作表对象. /// <summary> /// Excel 工作表. /// </summary> private static Microsoft.Office.Interop.Excel.Worksheet xlSheet =null; } }