C＃open excel读取错误 [英] C# open excel to read error

问题描述

您好，



我不知道为什么，但我无法打开excel文件。



这是我的代码：

Hello,

I don't know why, but I can't open an excel file.

This is my code:

private void button2_Click(object sender, EventArgs e)
{
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;

    object misValue = System.Reflection.Missing.Value;

    Excel.Application xlApp = new Excel.Application();

    if (xlApp == null)
    {
        MessageBox.Show("Excel is not properly installed!!");
        return;
    }

    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    xlWorkSheet= (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


    int _countcolumns = xlWorkSheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                Excel.XlSearchOrder.xlByColumns,
                Excel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Column;

    int _lastrow = xlWorkSheet.Cells.Find(
                                    "*",
                                    System.Reflection.Missing.Value,
                                    Excel.XlFindLookIn.xlValues,
                                    Excel.XlLookAt.xlWhole,
                                    Excel.XlSearchOrder.xlByRows,
                                    Excel.XlSearchDirection.xlPrevious,
                                    false,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value).Row;

    xlWorkBook.Close(true, null, null);
    xlApp.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

    MessageBox.Show("Last column used" + _countcolumns.ToString() + " and last row: " + _lastrow.ToString());
}

private void button1_Click(object sender, EventArgs e)
{
    string path;
    OpenFileDialog file = new OpenFileDialog();
    if (file.ShowDialog() == DialogResult.OK)
    {

        path = file.FileName;
        textBox1.Text = path;
    }
}

我收到错误

I get an error

System.Runtime.InteropServices.COMException (0x80010105)

。它表示我尝试打开Excel文件的行。









编辑******：

. It indicates to the line where i try to open Excel file.




EDIT ****** :

xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlExtractData);


Since i have over 4k cells with different format style and few cells with maybe over 200 chars , that was the best solution for me. Hope someone will find it useful !

我尝试了什么：



我在互联网上找到的一些提示，都没有。

What I have tried:

Few tips i found on internet, none worked.

推荐答案

您是否有权访问Excel文件？



无法看到如何将文件的名称输入文本框，但是，如果文本框中的所有内容都是没有路径的名称，则可能无效，请尝试使用OpenFileDialog确定确切的路径和名称。

Do you have privilege to access to the Excel file?

Cannot see how you get the name of the file into the textbox, but, if all that is in the text box is the name without a path it may not work, try using an OpenFileDialog to be certain of the exact path and name.

这是我写的一个用来加载Excel文件的类。它编译并按预期工作（我认为我正在使用Interop v15，但这不会对此代码产生任何影响）请随意修改以满足您的要求：

Here's a class I wrote to load Excel files. It compiles and works as intended (I'm using Interop v15 I think, but that shouldn't have any impact on this code) Feel free to modify to meet your requirements:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

/*
    Usage:
        string filename = System.IO.Path.Combine(BODCommon.Globals.MyDocuments, "alliedhealth_20140701143001275.xlsx");
        ExcelInterop ei = new ExcelInterop(filename);
        List<string> sheets = ei.GetSheetNames();
        // ...
        // Do something with the spreadsheet
        // ...
        ei.Close();
*/
namespace ExcelFunctions
{
    public static class WinAPI
    {
        [DllImport("user32.dll")]
        public static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
    }

    public class ExcelInterop
    {

        private Excel.Application excelApp = new Excel.Application();
        protected bool        IsLoaded { get; set; }
        protected string      FileName { get; set; }
        public Excel.Workbook Workbook { get ; set; }

        public ExcelInterop(string fileName)
        {
            this.IsLoaded = false;
            // the excel app is opened when this class is instantiated.
            this.FileName = fileName;
            this.Load();
        }

        public void Close()
        {
            try
            {
                uint iProcessID;
                WinAPI.GetWindowThreadProcessId((IntPtr)excelApp.Hwnd, out iProcessID);

                this.Workbook.Close(0, Missing.Value, Missing.Value);
                this.excelApp.Quit();

                System.Diagnostics.Process[] processes = System.Diagnostics.Process.GetProcessesByName("Excel");
                System.Diagnostics.Process process = processes.Where(x=>x.Id == iProcessID).FirstOrDefault();
                if (process != null)
                {
                    process.Kill();
                }
            }
            catch (Exception ex)
            {
            }
        }

        /// <summary>
        /// Loads the file specified in the constructor. If the file is a XLS file, it is converted 
        /// to XLSX. If the new filename already exists, the filename is adjust with "_0n" 
        /// depending on how many copies of the file exists.
        /// </summary>
        protected void Load()
        {
            if (string.IsNullOrEmpty(this.FileName))
            {
                throw new InvalidOperationException("Filename not specified.");
            }
            if (!File.Exists(this.FileName))
            {
                throw new InvalidOperationException(string.Format("File ({0}) not found.", this.FileName));
            }
            this.ConvertXlsToXlsx();
            this.Workbook = this.excelApp.Workbooks.Open(this.FileName, AddToMru:false);
            this.IsLoaded = true;
        }

        private void CheckLoaded()
        {
            if (!this.IsLoaded)
            {
                throw new Exception("File has not been loaded.");
            }
        }

        /// <summary>
        ///  Gets a list of worksheet names in the currently loaded workbook
        /// </summary>
        /// <returns></returns>
        public List<string> GetSheetNames()
        {
            this.CheckLoaded();
            List<string> names = new List<string>();
            {
                for (int i = 1; i <= this.Workbook.Sheets.Count; i++)
                {
                    names.Add(this.Workbook.Sheets[i].Name);
                }
            }
            return names;
        }

        /// <summary>
        /// Gets the active worksheet in the currently loaded workbook.
        /// </summary>
        /// <param name="name"></param>
        public void ActivateWorksheet(string name)
        {
            this.CheckLoaded();
            Excel._Worksheet worksheet = (Excel._Worksheet)(this.Workbook.Sheets[name]);
            worksheet.Activate();
        }

        #region used cell range methods

        /// <summary>
        /// Gets the used cell range for the currently active worksheet
        /// </summary>
        /// <returns></returns>
        public Excel.Range GetUsedRange()
        {
            this.CheckLoaded();
            Excel._Worksheet sheet = ((Excel._Worksheet)(this.Workbook.ActiveSheet));
            if (sheet == null)
            {
                throw new Exception("Active worksheet not set.");
            }
            Excel.Range usedRange = sheet.UsedRange;
            return usedRange;
        }

        /// <summary>
        /// Gets the used cell range for the named worksheet
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public Excel.Range GetUsedRange(string sheetName)
        {
            this.CheckLoaded();
            Excel._Worksheet sheet = ((Excel._Worksheet)(this.Workbook.Sheets[sheetName]));
            if (sheet == null)
            {
                throw new Exception(string.Format("Named worksheet ({0}) not found.", sheetName));
            }
            Excel.Range usedRange = sheet.UsedRange;
            return usedRange;
        }

        /// <summary>
        /// Gets the used columns in the currently active worksheet
        /// </summary>
        /// <returns></returns>
        public Excel.Range GetUsedColumns()
        {
            Excel.Range usedRange = this.GetUsedRange();
            return usedRange.Columns;
        }

        /// <summary>
        /// Gets the used columns in the named worksheet
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public Excel.Range GetUsedColumns(string sheetName)
        {
            Excel.Range usedRange = this.GetUsedRange(sheetName);
            return usedRange.Columns;
        }

        /// <summary>
        /// Gets the used rows in the currently active worksheet
        /// </summary>
        /// <returns></returns>
        public Excel.Range GetUsedRows()
        {
            Excel.Range usedRange = this.GetUsedRange();
            return usedRange.Rows;
        }

        /// <summary>
        /// Gets the used rows in the named worksheet
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public Excel.Range GetUsedRows(string sheetName)
        {
            Excel.Range usedRange = this.GetUsedRange(sheetName);
            return usedRange.Rows;
        }

        #endregion used cell range methods

        /// <summary>
        /// Converts the file from xls to xlsx by loading the xls, and then saving it in the 
        /// appropriate format, and with an "x" at the end of the extension. If the xlsx file 
        /// already exists, the filename will contain a version serial number (001-999) 
        /// indicating the newest version of the file. This method is called by the Load 
        /// method.
        /// </summary>
        public void ConvertXlsToXlsx()
        {
            if (this.FileName.ToUpper().EndsWith(".xls"))
            {
                string path = System.IO.Path.GetDirectoryName(this.FileName);
                string filename = System.IO.Path.GetFileNameWithoutExtension(this.FileName);

                string newName = string.Empty;
                string version = string.Empty;
                int count = 0;
                do
                {
                    newName = System.IO.Path.Combine(path, string.Concat(filename, version, ".xlsx"));
                    if (File.Exists(newName))
                    {
                        count++;
                        version = string.Format((count > 99) ? "_{0:000}" : "_{0:00}", count);
                    }
                    else
                    {
                        break;
                    }
                } while (true);
                this.FileName = newName;

                var workbook = this.excelApp.Workbooks.Open(this.FileName);
                workbook.SaveAs(Filename: this.FileName, FileFormat: Excel.XlFileFormat.xlOpenXMLWorkbook, AddToMru:false);
                workbook.Close();
            }
        }

        public string SaveSheetAsFile(string sheetName)
        {
            Excel._Worksheet sheet = this.Workbook.Sheets[sheetName];
            string filename = string.Empty;

            if (sheet != null)
            {
                filename = this.BuildFileName(sheetName);
                if (File.Exists(filename))
                {
                    File.Delete(filename);
                }
                // create a new workbook
                var newbook = this.excelApp.Workbooks.Add(1);
                // copy the desired sheet into it
                sheet.Copy(newbook.Sheets[1]);
                // delete the 2nd sheet
                newbook.Sheets[2].Delete();
                newbook.SaveAs(filename, FileFormat:Excel.XlFileFormat.xlWorkbookDefault, AddToMru:false);
                newbook.Close();
            }
            return filename;
        }

        private string BuildFileName(string sheetName)
        {
            string path = System.IO.Path.GetDirectoryName(this.FileName);
            string filename = System.IO.Path.GetFileNameWithoutExtension(this.FileName);
            string extension = System.IO.Path.GetExtension(this.FileName);
            filename = string.Format("{0} - {1}{2}", filename, sheetName, extension);
            string result = System.IO.Path.Combine(path, filename);
            return result;
        }

        public void Test()
        {
            Excel.Range range = (this.Workbook.ActiveSheet == null) ? this.GetUsedColumns(/*"Allied Health"*/) : this.GetUsedColumns(this.GetSheetNames().Last());
        }
    }
}

这篇关于C＃open excel读取错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！