excel html modify,Modify excel cell

问题

Good morning,

I would like to edit some cells from already existing excell file. I tried use EPPlus and normal OpenXml classes. However I failed. In both situation program won't crash but always return old (not modified) excel. Please, what am I doing wrong?

Trial 1 - EPPlus:

MemoryStream memoryStream = new MemoryStream();

using (var fs = new FileStream(@"Path\Test.xlsx", FileMode.Open, FileAccess.Read))

{

byte[] buffer = new byte[1024];

int bytesRead = 0;

while ((bytesRead = fs.Read(buffer, 0, buffer.Length)) > 0)

{

memoryStream.Write(buffer, 0, bytesRead);

}

}

using (ExcelPackage excelPackage = new ExcelPackage(memoryStream))

{

ExcelWorkbook excelWorkBook = excelPackage.Workbook;

ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First();

excelWorksheet.Cells[1, 1].Value = "Test";

excelWorksheet.Cells[3, 2].Value = "Test2";

excelWorksheet.Cells[3, 3].Value = "Test3";

excelPackage.Save();

}

memoryStream.Position = 0;

return new FileStreamResult(memoryStream, "application/xlsx")

{

FileDownloadName = "Tester.xlsx"

};

How i said it returns old excel. But in debug mode it contains new value also. It looks like memoryStream cannot be modified.

Trial 2 - OpenXml classes

Stream stream = System.IO.File.Open(@"Path\Test.xlsx", FileMode.Open);

using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true))

{

WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Sheet1");

Cell cell = GetCell(worksheetPart.Worksheet, "C", 3);

cell.CellValue = new CellValue("Testos");

cell.DataType = new EnumValue(CellValues.String);

worksheetPart.Worksheet.Save();

}

stream.Position = 0;

return new FileStreamResult(stream, "application/xlsx")

{

FileDownloadName = "Tester.xlsx"

};

And helper methods:

private static Row GetRow(Worksheet worksheet, uint rowIndex)

{

Row row;

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

{

row = worksheet.Elements().Where(r => r.RowIndex == rowIndex).FirstOrDefault();

}

else

{

row = new Row() { RowIndex = rowIndex };

worksheet.Append(row);

}

return row;

}

private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)

{

Row row = GetRow(worksheet, rowIndex);

string cellReference = columnName + rowIndex;

if (row.Elements().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)

{

return row.Elements().Where(c => c.CellReference.Value == cellReference).FirstOrDefault();

}

else

{

Cell refCell = null;

foreach (Cell cell in row.Elements())

{

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;

}

}

private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)

{

IEnumerable sheets = document.WorkbookPart.Workbook.GetFirstChild().Elements().Where(s => s.Name == sheetName);

string relationshipId = sheets.First().Id.Value;

WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

return worksheetPart;

}

Once more thank you for help.

回答1:

The problem in both cases is that the modified workbook is not saved back to the stream:

MemoryStream ms = new MemoryStream();

using (FileStream fs = File.OpenRead(@"Path\Test.xlsx"))

using (ExcelPackage excelPackage = new ExcelPackage(fs))

{

ExcelWorkbook excelWorkBook = excelPackage.Workbook;

ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First();

excelWorksheet.Cells[1, 1].Value = "Test";

excelWorksheet.Cells[3, 2].Value = "Test2";

excelWorksheet.Cells[3, 3].Value = "Test3";

excelPackage.SaveAs(ms); // This is the important part.

}

ms.Position = 0;

return new FileStreamResult(ms, "application/xlsx")

{

FileDownloadName = "Tester.xlsx"

};

回答2:

although this is answered I'll add from my experience.

It is easier to open the ExcelPackage from FileInfo instead of Stream, then saving becomes simpler.

FileInfo file = new FileInfo(path);

using (var package = new ExcelPackage(file))

{

ExcelWorkbook workBook = package.Workbook;

ExcelWorksheet currentWorksheet = workBook.Worksheets.SingleOrDefault(w => w.Name == "sheet1");

int totalRows = currentWorksheet.Dimension.End.Row;

int totalCols = currentWorksheet.Dimension.End.Column;

for (int i = 2; i <= totalRows; i++)

{

try

{

currentWorksheet.Cells[i, 1].Value = "AAA";

}

catch (Exception ex)

{

_logger.Error(String.Format("Error: failed editing excel. See details: {0}", ex));

return;

}

}

package.Save();

回答3:

You can use the Interop dll's from Microsoft to edit office documents http://msdn.microsoft.com/en-us/library/15s06t57.aspx. Add the "Microsoft.Office.Interop.Excel.dll" to your solution. With this code i've changed 2 cell values.

static void Main(string[] args)

{

Application excel = new Application();

Workbook workbook = excel.Workbooks.Open(@"C:\Users\Martijn\Documents\Test.xlsx", ReadOnly: false, Editable:true);

Worksheet worksheet = workbook.Worksheets.Item[1] as Worksheet;

if (worksheet == null)

return;

Range row1 = worksheet.Rows.Cells[1, 1];

Range row2 = worksheet.Rows.Cells[2, 1];

row1.Value = "Test100";

row2.Value = "Test200";

excel.Application.ActiveWorkbook.Save();

excel.Application.Quit();

excel.Quit();

}

I've started with Test1 and Test2 wich after the program running changed into the proper values.

回答4:

I'm using ClosedXML where updating a cell value is a no-brainer:

var workbook = new XLWorkbook("HelloWorld.xlsx"); // load the existing excel file

var worksheet = workbook.Worksheets.Worksheet(1);

worksheet.Cell("A1").SetValue("Hello World!");

workbook.Save();

The NuGet package can be found here.

回答5:

//this help you to set cells in file excel : using System.Data.OleDb;

string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Directory.GetCurrentDirectory() + "/swtlist.xlsx;" +

@"Extended Properties='Excel 12.0;HDR=Yes;';Persist Security Info=False;";

using (OleDbConnection connection = new OleDbConnection(connString))

{

connection.Open();

try

{

OleDbCommand cmd = new OleDbCommand("UPDATE [Feuil1$] SET d='yes' ", connection);

cmd.ExecuteNonQuery();

connection.Close();

}

catch (Exception ex) { }

}

来源：https://stackoverflow.com/questions/23168657/modify-excel-cell