If somebody like the post and its helpful in your work then, add comments.

Monday, July 27, 2009

SpredSheetML : Updating Column in Excel sheet

Hi,

Thw basic thing in excel is to update the cell in the sheet. We have know idea how to do that. From packaging class method it is diffcult/complez to update specific row column. The SPreadsheetML makes more easy to do that. We have to just add reference of the DocumentFormat.OpenXml and write below code in the solution.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ExcelUpdation
{
class Program
{
static void Main(string[] args)
{
// InsertText(@"c:\SummaryGraphs.xlsx", "123456789");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "C");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "D");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "E");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "F");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "G");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "H");

UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "C");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "D");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "E");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "F");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "G");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "H");


}

#region Approch 1

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, "Summary"); //Sheet Title in work book
if (worksheetPart != null)
{ Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType = new EnumValue(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Save();
}
}
}
private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
IEnumerable sheets = document.WorkbookPart.Workbook.GetFirstChild(). Elements().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().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().
Elements().Where(r => r.RowIndex == rowIndex).First();
}
#endregion

}
}

The reference link
Click Here!

No comments:

Post a Comment