使用aopenxml读取excel文件并写入路径 [英] reading a excel file using aopenxml and write into a a path

问题描述

HI团队，

您能否帮助获取参考或代码片段以阅读excel文件 并撰写使用open xml dll进入新位置。

我从论坛获取的代码是创建excel但已损坏并且不存在完整数据。

谢谢

解决方案

您好
SimpleCodes，

阅读excel文件，您可以尝试参考下面的代码。

 // DOM方法。 
 //请注意，下面的代码仅适用于包含数值的单元格。 
 // 
 static void ReadExcelFileDOM（string fileName）
 {
 using（SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open（fileName，false））
 {
 WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; 
 WorksheetPart worksheetPart = workbookPart.WorksheetParts.First（）; 
 SheetData sheetData = worksheetPart.Worksheet.Elements< SheetData>（）。First（）; 
字符串文字; 
 foreach（在SheetData.Elements中的行r< Row>（））
 {
 foreach（r.Elements中的Cell c< Cell>（））
 {
 text = c.CellValue.Text; 
 Console.Write（text +""）; 
} 
} 
 Console.WriteLine（）; 
 Console.ReadKey（）; 
} 
} 
 
 // SAX方法。 
 static void ReadExcelFileSAX（string fileName）
 {
 using（SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open（fileName，false））
 {
 WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; 
 WorksheetPart worksheetPart = workbookPart.WorksheetParts.First（）; 
 
 OpenXmlReader reader = OpenXmlReader.Create（worksheetPart）; 
字符串文字; 
 while（reader.Read（））
 {
 if（reader.ElementType == typeof（CellValue））
 {
 text = reader.GetText（）; 
 Console.Write（text +""）; 
} 
} 
 Console.WriteLine（）; 
 Console.ReadKey（）; 
} 
} 
 

参考：

如何：解析并阅读大型电子表格文档（Open XML SDK）

to编写excel文件，你可以参考下面的代码。

 //给定一个文档名称和文本，
 //插入一个新的工作表并写入文本单元格"A1"表示单元格"A1"。新工作表。 
 
 public static void InsertText（string docName，string text）
 {
 //打开文档进行编辑。 
 using（SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open（docName，true））
 {
 //获取SharedStringTablePart。如果它不存在，请创建一个新的。 
 SharedStringTablePart shareStringPart; 
 if（spreadSheet.WorkbookPart.GetPartsOfType< SharedStringTablePart>（）。Count（）> 0）
 {
 shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType< SharedStringTablePart>（）。First（）; 
} 
 else 
 {
 shareStringPart = spreadSheet.WorkbookPart.AddNewPart< SharedStringTablePart>（）; 
} 
 
 //将文本插入SharedStringTablePart。 
 int index = InsertSharedStringItem（text，shareStringPart）; 
 
 //插入新工作表。 
 WorksheetPart worksheetPart = InsertWorksheet（spreadSheet.WorkbookPart）; 
 
 //将单元格A1插入新工作表。 
 Cell cell = InsertCellInWorksheet（" A"，1，worksheetPart）; 
 
 //设置单元格A1的值。 
 cell.CellValue = new CellValue（index.ToString（））; 
 cell.DataType = new EnumValue< CellValues>（CellValues.SharedString）; 
 
 //保存新工作表。 
 worksheetPart.Worksheet.Save（）; 
} 
} 
 
 //给定文本和SharedStringTablePart，创建一个具有指定文本
 //的SharedStringItem，并将其插入SharedStringTablePart。如果该项已存在，则返回其索引。 
 private static int InsertSharedStringItem（string text，SharedStringTablePart shareStringPart）
 {
 //如果部件不包含SharedStringTable，请创建一个。 
 if（shareStringPart.SharedStringTable == null）
 {
 shareStringPart.SharedStringTable = new SharedStringTable（）; 
} 
 
 int i = 0; 
 
 //迭代SharedStringTable中的所有项目。如果文本已存在，则返回其索引。 
 foreach（shareStringPart.SharedStringTable.Elements< SharedStringItem>（）中的SharedStringItem项目）
 {
 if（item.InnerText == text）
 {
 return i; 
} 
 
 i ++; 
} 
 
 //部件中不存在该文本。创建SharedStringItem并返回其索引。 
 shareStringPart.SharedStringTable.AppendChild（new SharedStringItem（new DocumentFormat.OpenXml.Spreadsheet.Text（text）））; 
 shareStringPart.SharedStringTable.Save（）; 
 
返回i; 
} 
 
 //给定WorkbookPart，插入一个新的工作表。 
 private static WorksheetPart InsertWorksheet（WorkbookPart workbookPart）
 {
 //在工作簿中添加一个新的工作表部分。 
 WorksheetPart newWorksheetPart = workbookPart.AddNewPart< WorksheetPart>（）; 
 newWorksheetPart.Worksheet = new Worksheet（new SheetData（））; 
 newWorksheetPart.Worksheet.Save（）; 
 
 Sheets sheets = workbookPart.Workbook.GetFirstChild< Sheets>（）; 
 string relationshipId = workbookPart.GetIdOfPart（newWorksheetPart）; 
 
 //获取新工作表的唯一ID。 
 uint sheetId = 1; 
 if（sheets.Elements< Sheet>（）。Count（）> 0）
 {
 sheetId = sheets.Elements< Sheet>（）。选择（s => s。 SheetId.Value）.Max（）+ 1; 
} 
 
 string sheetName =" Sheet" + sheetId; 
 
 //附加新工作表并将其与工作簿关联。 
工作表=新工作表（）{Id = relationshipId，SheetId = sheetId，Name = sheetName}; 
 sheets.Append（sheet）; 
 workbookPart.Workbook.Save（）; 
 
返回newWorksheetPart; 
} 
 
 //给定列名，行索引和WorksheetPart，将单元格插入工作表。 
 //如果单元格已存在，则返回它。 
 private static Cell InsertCellInWorksheet（string columnName，uint rowIndex，WorksheetPart worksheetPart）
 {
 Worksheet worksheet = worksheetPart.Worksheet; 
 SheetData sheetData = worksheet.GetFirstChild< SheetData>（）; 
 string cellReference = columnName + rowIndex; 
 
 //如果工作表不包含具有指定行索引的行，请插入一行。 
行排; 
 if（sheetData.Elements< Row>（）。Where（r => r.RowIndex == rowIndex）.Count（）！= 0）
 {
 row = sheetData.Elements< ; Row>（）。其中​​（r => r.RowIndex == rowIndex）.First（）; 
} 
 else 
 {
 row = new Row（）{RowIndex = rowIndex}; 
 sheetData.Append（row）; 
} 
 
 //如果没有指定列名的单元格，请插入一个。 
 if（row.Elements< Cell>（）。Where（c => c.CellReference.Value == columnName + rowIndex）.Count（）> 0）
 {
 return row.Elements< Cell>（）。where（c => c.CellReference.Value == cellReference）.First（）; 
} 
 else 
 {
 //根据CellReference，单元格必须按顺序排列。确定插入新单元格的位置。 
 Cell refCell = null; 
 foreach（行中的单元格。元素< Cell>（））
 {
 if（string.Compare（cell.CellReference.Value，cellReference，true）> 0）
 {
 refCell = cell; 
休息; 
} 
} 
 
 Cell newCell = new Cell（）{CellReference = cellReference}; 
 row.InsertBefore（newCell，refCell）; 
 
 worksheet.Save（）; 
返回newCell; 
} 
} 
 

参考：

如何：将文本插入电子表格文档中的单元格（Open XML SDK）

为了满足您的要求，您需要借助上述代码开发自己的解决方案。你需要合并这两个代码并根据你的要求修改它。

问候

Deepak

HI Team,

Can you help to get the reference or code snippets to read an excel file  and write that in  to a new location using open xml dll.

The code I got fro the forums is creating excel but corrupted and complete data is not present .

thanks

解决方案

Hi SimpleCodes,

to read the excel file you can try to refer code below.

// The DOM approach.
// Note that the code below works only for cells that contain numeric values.
// 
static void ReadExcelFileDOM(string fileName)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
        string text;
        foreach (Row r in sheetData.Elements<Row>())
        {
            foreach (Cell c in r.Elements<Cell>())
            {
                text = c.CellValue.Text;
                Console.Write(text + " ");
            }
        }
        Console.WriteLine();
        Console.ReadKey();
    }
}

// The SAX approach.
static void ReadExcelFileSAX(string fileName)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
        string text;
        while (reader.Read())
        {
            if (reader.ElementType == typeof(CellValue))
            {
                text = reader.GetText();
                Console.Write(text + " ");
            }
        }
        Console.WriteLine();
        Console.ReadKey();
    }
}

Reference:

How to: Parse and read a large spreadsheet document (Open XML SDK)

to write the excel file you can refer code below.

// Given a document name and text, 
 // inserts a new work sheet and writes the text to cell "A1" of the new worksheet.

 public static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the text into the SharedStringTablePart.
        int index = InsertSharedStringItem(text, shareStringPart);

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

        // Insert cell A1 into the new worksheet.
        Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

        // Set the value of cell A1.
        cell.CellValue = new CellValue(index.ToString());
        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

        // Save the new worksheet.
        worksheetPart.Worksheet.Save();
    }
}

        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }

        // Given a WorkbookPart, inserts a new worksheet.
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }

        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }

Reference:

How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

to fulfill your requirement , you need to develop your own solution with the help of code above. you need to merge the both code and modify it according to your requirement.

Regards

Deepak

这篇关于使用aopenxml读取excel文件并写入路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！