Excel Report Generator [Generic Class]
Create a class file [GenerateReport.cs] and add the following
=============================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
namespace SharePointProject1
{
public static class ReportGenerator
{
static WorkbookPart wbPart = null;
public static bool GenerateReport(string filePath, DataSet sheetDataColl)
{
bool isGenerated = false;
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
{
wbPart = document.WorkbookPart;
foreach (DataTable sheetData in sheetDataColl.Tables)
{
string sheetName = sheetData.TableName;
foreach (DataRow row in sheetData.Rows)
{
UpdateValue(sheetName, Convert.ToString(row["Column"]), Convert.ToUInt32(row["Row"]), Convert.ToString(row["Value"]), 0, true);
}
}
isGenerated = true;
}
}
catch (Exception oEx)
{
isGenerated = false;
}
return isGenerated;
}
private static bool UpdateValue(string sheetName, string column, uint row, string value,
UInt32Value styleIndex, bool isString)
{
// Assume failure.
bool updated = false;
// document.Close();
Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(
(s) => s.Name == sheetName).FirstOrDefault();
if (sheet != null)
{
Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
// Insert cell A1 into the new worksheet.
Cell cell = InsertCellInWorksheet(column, row, ws);
if (isString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = InsertSharedStringItem(wbPart, value);
// Set the value of cell A1.
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
// Save the new worksheet.
ws.Save();
//cell.CellValue = new CellValue(stringIndex.ToString());
// cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(value);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
if (styleIndex > 0)
cell.StyleIndex = styleIndex;
// Save the worksheet.
ws.Save();
updated = true;
}
return updated;
}
// Given the main workbook part, and a text value, insert the text into
// the shared string table. Create the table if necessary. If the value
// already exists, return its index. If it doesn't exist, insert it and
// return its new index.
private static int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
int index = 0;
bool found = false;
var stringTablePart = wbPart
.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTablePart == null)
{
// Create it.
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
//stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
stringTablePart.SharedStringTable = new SharedStringTable();
}
var stringTable = stringTablePart.SharedStringTable;
// Iterate through all the items in the SharedStringTable.
// If the text already exists, return its index.
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTable.Save();
}
return index;
}
// Add a cell with the specified address to a row.
private static Cell CreateCell(Row row, String address)
{
Cell cellResult;
Cell refCell = null;
// Cells must be in sequential order according to CellReference.
// Determine where to insert the new cell.
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, address, true) > 0)
{
refCell = cell;
break;
}
}
cellResult = new Cell();
cellResult.CellReference = address;
row.InsertBefore(cellResult, refCell);
return cellResult;
}
// Return the row at the specified rowIndex located within
// the sheet data passed in via wsData. If the row does not
// exist, create it.
private static Row GetRow(SheetData wsData, UInt32 rowIndex)
{
var row = wsData.Elements<Row>().
Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
if (row == null)
{
row = new Row();
row.RowIndex = rowIndex;
wsData.Append(row);
}
return row;
}
// Given an Excel address such as E5 or AB128, GetRowIndex
// parses the address and returns the row index.
private static UInt32 GetRowIndex(string address)
{
string rowPart;
UInt32 l;
UInt32 result = 0;
for (int i = 0; i < address.Length; i++)
{
if (UInt32.TryParse(address.Substring(i, 1), out l))
{
rowPart = address.Substring(i, address.Length - i);
if (UInt32.TryParse(rowPart, out l))
{
result = l;
break;
}
}
}
return result;
}
// 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, Worksheet worksheet)
{
//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;
}
}
}
}
====================== Using the above class in a web part ================
protected void Button1_Click(object sender, EventArgs e)
{
string path = "c:\\test\\";
string dest = path + "One.xlsx";
string source = path + "Test.xlsx";
File.Copy(source, dest, true);
FileInfo fi = new FileInfo(dest);
if (fi.IsReadOnly)
{
fi.IsReadOnly = false;
}
ReportGenerator.GenerateReport(dest, GetSheetData());
Label1.Text = "Generated Successfully....";
}
//Create a sample datatables and add it to dataset
public DataSet GetSheetData()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable("Sheet1");
dt.Columns.Add("Column");
dt.Columns.Add("Row");
dt.Columns.Add("Value");
dt.Rows.Add("E", 8, "Test1 From Fynamic");
dt.Rows.Add("E", 9, "Test1 From Fynamic");
dt.Rows.Add("E", 10, "Test1 From Fynamic");
dt.Rows.Add("E", 11, "Test1 From Fynamic");
dt.Rows.Add("E", 12, "Test1 From Fynamic");
dt.Rows.Add("E", 13, "Test1 From Fynamic");
DataTable dt2 = new DataTable("Sheet2");
dt2.Columns.Add("Column");
dt2.Columns.Add("Row");
dt2.Columns.Add("Value");
dt2.Rows.Add("E", 8, "Test1 From Fynamic");
dt2.Rows.Add("E", 9, "Test1 From Fynamic");
dt2.Rows.Add("E", 10, "Test1 From Fynamic");
dt2.Rows.Add("E", 11, "Test1 From Fynamic");
dt2.Rows.Add("E", 12, "Test1 From Fynamic");
dt2.Rows.Add("E", 13, "Test1 From Fynamic");
ds.Tables.Add(dt);
ds.Tables.Add(dt2);
return ds;
}
|
