So, .NET has no built-in Office features?

Question

I always thought it was, although I don’t know where I got the idea from ... I always thought it was easy to process a spreadsheet as a 2D array, for example, but some searches on SO show that everyone is using third-party libraries? Or do all these people need solutions that do not require the installation of Office ... if I do not have this limitation, will you feel better?

As I said, I looked at SO, but did not see the answer to this specific question. However, maybe my search skills just suck ...

EDIT: The idea is that I want to open an XLS / CSV document in a C # application. I don’t want to get any complex data from the cells, just being able to read the text value from each cell. Ideally, a wrapper that processes a spreadsheet, such as a 2D array of cells using the Cell.getText () method, is all I need.

Answer 1

Yes, .NET has built-in Office functionality . But you will beat yourself trying to use it. It is also well hidden and only compatible with Office 2007 and later (unless you download the compatibility add-on for Office 2003 / XP).

API, Office, . Office Open XML SDK, .xx Office (.docx,.xslx ..).

---

, System.IO.Packaging Office, -:

http://msdn.microsoft.com/en-us/library/dd371623(VS.85).aspx

, Open XML-. Open XML? (, ) XML, ZIP . Office, .zip .

? , , XDocuments . , , XML, , .. .. ..

System.IO.Packaging , , ( ), .

, , Open XML. , . , Open XML, .

MS Open XML SDK, System.IO.Packaging, , Open XML.

.

---

, . xls ; - 2D-. API, .

Office 2007 , Office Open XML SDK. (Office 2003, XP), Excel codeplex.com( , Excel Data Reader). , Excel. [x] [y] .

Answer 2

.XLS(Excel 97-2003), *.XLSX, JET Provider:

OleDbConnection con = new OleDbConnection(string.Format(
                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"",
                 "filename.xls"
                 ));
con.Open();
OleDbDataAdapter ad = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);
DataTable t = new DataTable();
ad.Fill(t);

DataTable, .

Answer 3

Visual Basic (, , ), .NET Office, #, .

.

Answer 4

, , Office Visual Studio.

Excel Visual Studio 2005

Visual Studio Office.

Answer 5

, Excel Data Reader . Microsoft Excel ('97 -2007) .

( ):

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

Answer 6

,.NET Office.

Answer 7

Visual Studio Tools for Office.

Answer 8

XML-, XML, Office Interop. . . XML , .

Answer 9

Visual Studio Tools for Office? API Office.

, VSTO, .. API VSTO, LINQ ..

VSTO

Answer 10

. . Interop. , COM-, .

, , Office 2007 - ( ), , , UI/, Interop.

, ADO, , , , .

, . FlexCel , . , .

Answer 11

, ... , Office .Net-? .Net, VBA.

, Office Visual Studio, -, "" Office. - .

Answer 12

http://www.codeplex.com/xlslinq

Worksheets .

using(XlsWorkbook book = new XlsWorkbook("TestData\\100.xls")) {

    var sheets = from s in book.Worksheets
                 where s.Name == "100"
                 select s;

    foreach(var sheet in sheets) Console.WriteLine(sheet.Name);
}

Answer 13

The interface to Office is not so easy to use. An example of a small example here (below) is opening a worksheet, parsing an entire thing looking for a specific “hit” using RegExp:

    internal void OpenSearchAndReplace(string path, Logger log)
    {
        object nullobj = System.Reflection.Missing.Value;
        ConfigurationManager conf = new ConfigurationManager();

        try
        {
            if (_excelApp == null)
                _excelApp = new Excel.Application();

            Excel.Workbook book = _excelApp.Workbooks.Open(path, nullobj, nullobj, nullobj, nullobj, nullobj, nullobj,
                                                           nullobj, nullobj, nullobj, nullobj,
                                                           nullobj, nullobj, nullobj, nullobj);
            Excel.Worksheet worksheet;
            if( book.Worksheets.Count > 1 )
                worksheet = (Excel.Worksheet)book.Worksheets.get_Item("Sheet1");
            else
                worksheet = (Excel.Worksheet)book.ActiveSheet;

            Excel.Range range = worksheet.UsedRange;

            object[,] values = (object[,])range.Value2;

            for (int row = 1; row <= values.GetUpperBound(0); ++row)
            {
                for (int col = 1; col <= values.GetUpperBound(1); ++col)
                {
                    string value = Convert.ToString(values[row, col]);
                    if (Regex.IsMatch(value, @conf.GetFullyQualifiedRegExp()))
                    {
                        range.Cells.set_Item(row, col, conf.GetReplacementText());
                    }
                }
            }
            book.Save();
            Marshal.ReleaseComObject(worksheet );

            log.LogExcelFile( "File " + path + " has been processed\n" );
        }
        catch (Exception ex)
        {...}

But please do not forget to force Garbagecollector if you do not want to have many objects hanging around me :):

            // Force the garbagecollector to kill objects. 
            // Waiting for it to finish
            GC.Collect();
            GC.WaitForPendingFinalizers();