static void Main(string[] args)
{
var data = new DataSet();
var sheet1 = new DataTable();
var sheet2 = new DataTable();
List<string> sheetNames = new List<string>() { "First Sheet", "Second Sheet" };
//Sheet 1 Headers
sheet1.Columns.Add("Test Header For Strings", typeof(string));
sheet1.Columns.Add("Test Header For Numbers", typeof(int));
sheet1.Columns.Add("Test Header For Decimals", typeof(double));
sheet1.Columns.Add("Test Header For HyperLinks", typeof(string));
//Sheet 1 Data
sheet1.Rows.Add("Test Val 1", 1, 1.50, "http://www.google.com/search?q=Ben+Carson");
sheet1.Rows.Add("Test Val 2", 160, 34.50, "http://www.google.com/search?q=Donald+Trump");
sheet1.Rows.Add("Test Val 3", 540, 9.99, "http://www.google.com/search?q=Hillary+Clinton");
sheet2.Columns.Add("Test Header For Strings", typeof(string));
sheet2.Columns.Add("Test Header For Numbers", typeof(int));
sheet2.Columns.Add("Test Header For Decimals", typeof(double));
sheet2.Columns.Add("Test Header For HyperLinks", typeof(string));
//Sheet 1 Data
sheet2.Rows.Add("Ichigo", 1, 1.50, "http://www.google.com/search?q=Bankai");
sheet2.Rows.Add("Goku", 160, 34.50, "http://www.google.com/search?q=Dattebayo");
data.Tables.Add(sheet1);
data.Tables.Add(sheet2);
var wbData = new WorkBookData(data, sheetNames);
var xlGen = new Utils.ExcelGenerator();
xlGen.CreateSpreadSheet(@"C:\Users\twong\Documents\xlgentest\output.xlsx", wbData);
}
// Creates a SpreadsheetDocument.
public void CreateSpreadSheet(string filePath, WorkBookData data)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
CreateParts(package, data);
}
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document, WorkBookData data)
{
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPartContent(workbookPart1, data.sheetNames);
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId" + (data.data.Tables.Count + 1));
GenerateThemePart1Content(themePart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId" + (data.data.Tables.Count + 2));
GenerateWorkbookStylesPartContent(workbookStylesPart1);
int wsID = 1;
WorksheetPart wsPart;
DataRow currentRow;
foreach (DataTable dt in data.data.Tables)
{
wsPart = workbookPart1.AddNewPart<WorksheetPart>("rId" + wsID++);
GenerateWorksheetPartContent(wsPart, dt, wsID <= 2);
//Adding HyperLinkRelationships
int idTracker = 1;
for (int ii = 0; ii < dt.Rows.Count; ii++)
{
currentRow = dt.Rows[ii];
for (int jj = 0; jj < dt.Columns.Count; jj++)
{
if (dt.Columns[jj].DataType == typeof(string) && currentRow[jj].ToString().ToLower().Contains("http"))
{
wsPart.AddHyperlinkRelationship(new System.Uri(currentRow[jj].ToString(), System.UriKind.Absolute), true, "rId" + idTracker++);
}
}
}
}
//SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId4");
//GenerateSharedStringTablePart1Content(sharedStringTablePart1);
SetPackageProperties(document);
}