examples - documentformat openxml read excel c#

Open XML SDK 2.0: ¿cómo actualizar una celda en una hoja de cálculo?

Question

Quiero actualizar una celda en una hoja de cálculo que usa un gráfico, usando Open XML SDK 2.0 (CTP). Todas las muestras de código que he encontrado insertan nuevas celdas. Estoy luchando con recuperar la hoja de trabajo correcta.

public static void InsertText(string docName, string text, uint rowIndex,
string columnName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet =
SpreadsheetDocument.Open(docName, true))
{
Workbook workBook = spreadSheet.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workBook.WorkbookPart.
WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.
GetFirstChild<SheetData>();
// 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)
// At this point I am expecting a match for a row that exists
// in sheet1 but I am not getting one

Cuando navego por el árbol en Visual Studio, veo tres hojas, pero ninguna de ellas tiene hijos. ¿Qué me estoy perdiendo?

Answer 1

Aquí está el código de trabajo. Este es un prototipo. Para un número mayor de cambios, uno puede abrir el documento solo una vez. Además, hay algunos elementos codificados como el nombre de la hoja y el tipo de celda que deberían parametrizarse antes de que esto pueda denominarse listo para producción. http://openxmldeveloper.org/forums/4005/ShowThread.aspx fue muy útil.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.IO;
using System.Diagnostics;
namespace OpenXMLWindowsApp
{
public class OpenXMLWindowsApp
{
public void UpdateSheet()
{
UpdateCell("Chart.xlsx", "20", 2, "B");
UpdateCell("Chart.xlsx", "80", 3, "B");
UpdateCell("Chart.xlsx", "80", 2, "C");
UpdateCell("Chart.xlsx", "20", 3, "C");
ProcessStartInfo startInfo = new ProcessStartInfo("Chart.xlsx");
startInfo.WindowStyle = ProcessWindowStyle.Normal;
Process.Start(startInfo);
}
public static void UpdateCell(string docName, string text,
uint rowIndex, string columnName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet =
SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart =
GetWorksheetPartByName(spreadSheet, "Sheet1");
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet,
columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType =
new EnumValue<CellValues>(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Save();
}
}
}
private static WorksheetPart
GetWorksheetPartByName(SpreadsheetDocument document,
string sheetName)
{
IEnumerable<Sheet> sheets =
document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return null;
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)
document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
// Given a worksheet, a column name, and a row index,
// gets the cell at the specified column and
private static Cell GetCell(Worksheet worksheet,
string columnName, uint rowIndex)
{
Row row = GetRow(worksheet, rowIndex);
if (row == null)
return null;
return row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).First();
}
// Given a worksheet and a row index, return the row.
private static Row GetRow(Worksheet worksheet, uint rowIndex)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
}
}

Answer 2

El código publicado por @CDonner arroja algunas excepciones, he agregado algo del código que se encargará del código, que arroja una excepción, aquí está

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.IO;
using System.Diagnostics;
namespace Application.Model{
public class TempCode
{
public TempCode()
{
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "120", 1, "A");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "220", 2, "B");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "320", 3, "C");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "420", 4, "D");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "520", 5, "E");
ProcessStartInfo startInfo = new ProcessStartInfo("E:/Visual Studio Code/Book1.xlsx");
startInfo.WindowStyle = ProcessWindowStyle.Normal;
Process.Start(startInfo);
}
public static void UpdateCell(string docName, string text,uint rowIndex, string columnName){
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Sheet2");
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Save();
}
}
}
private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName){
IEnumerable<Sheet> sheets =document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0){
return null;
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
Row row;
string cellReference = columnName + rowIndex;
if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
row = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
else{
row = new Row() { RowIndex = rowIndex };
worksheet.Append(row);
}
if (row == null)
return null;
if (row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).Count() > 0) {
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else{
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,
StyleIndex = (UInt32Value)1U
};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
}

}

Answer 3

Esto es SDK 2.5 aunque, sin embargo, fue un código muy útil que se encuentra aquí: http://fczaja.blogspot.dk/2013/05/how-to-read-and-write-excel-cells-with.html

Es necesario hacer una pequeña modificación para los valores de texto para agregarlos a SharedStringTablePart .

// 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 Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}

Y úsalo así:

SharedStringTablePart shareStringPart = GetSharedStringTablePart(excelDoc);
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(cellValue, shareStringPart);
// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

Answer 4

He estado trabajando con Excel y encontré que esta biblioteca auxiliar es de gran ayuda (he creado mis propios ayudantes por palabra, habría ahorrado al menos 2 semanas si tuviera conocimiento de esto): http://simpleooxml.codeplex.com/

Esto es lo que se necesita para actualizar cell (writer.PasteText (...)):

MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteText("B2", "Hello World");
//Save to the memory stream
SpreadsheetWriter.Save(doc);
byte[] result = stream.ToArray();
FileStream file = new FileStream(@"D:/x1.xlsx", FileMode.Create);
file.Write(result, 0, result.Length);
file.Close();

Answer 5

Hice algunos cambios en el código @AZ.

Primero, en la función GetCell hay un problema al seleccionar la fila actual. Solo cambia:

if (worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)

en lugar de:

if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)

Y en la sección:

if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)

Si está utilizando Columnas arriba Columna Z (como columna AA, por ejemplo) no funcionará correctamente. Para algunos esto, estoy usando los números de columna para determinar dónde insertar la Celda.

Para esto, creé una función ColumnIndex, con la que convertí las letras de las columnas a números:

private static int ColumnIndex(string reference)
{
int ci = 0;
reference = reference.ToUpper();
for (int ix = 0; ix < reference.Length && reference[ix] >= ''A''; ix++)
ci = (ci * 26) + ((int)reference[ix] - 64);
return ci;
}

Así que cambié la función de comparación de cadenas para esto:

string columnNew = new String(cellReference.Where(c => c != ''-'' && (c < ''0'' || c > ''9'')).ToArray());
foreach (Cell cell in row.Elements<Cell>())
{
string columnBase = new String(cell.CellReference.Value.Where(c => c != ''-'' && (c < ''0'' || c > ''9'')).ToArray());
if (ColumnIndex(columnBase) > ColumnIndex(columnNew))
{
refCell = cell;
break;
}
}

Atentamente.