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); }