public static WorksheetsCollection GetAllWorksheets(string fileName) { //Initialize a new Workboook object Workbook workbook = new Workbook(); //Load the document workbook.LoadFromFile(fileName); //Get all worksheets WorksheetsCollection worksheets = workbook.Worksheets; return(worksheets); }
// Add table to word #region converting tables public void Add(string filepath, List <string> names, bool boolResult, bool boolSteel) { //Initialize an instance of Workbook class and load excel from file Workbook workbook = new Workbook(); workbook.LoadFromFile(filepath); //Get all worksheets WorksheetsCollection worksheets = workbook.Worksheets; Section section1 = doc.Sections[0]; Worksheet sheet; Paragraph title; Table table; List <int> ColumnList = new List <int>(); // Fit result's column in one table if (boolResult) { numcol = 99; names.Clear(); foreach (var item in worksheets) { names.Add(item.Name); } } foreach (var name in names) { int iter = 0; foreach (Worksheet item in worksheets) { if (name == item.Name) { //Get the worksheet sheet = workbook.Worksheets[iter]; // Get which column to export ColumnList.Clear(); if (!boolResult) { ColumnList = GetColumnList(name, boolSteel); } else { for (int i = 1; i <= sheet.LastColumn; i++) { ColumnList.Add(i); } } if (ColumnList.Count <= numcol) // 1 part { //Write Name of Worksheet title = section1.AddParagraph(); title.Text = item.Name; title.ApplyStyle(style.Name); //Write Table table = section1.AddTable(true); table.ResetCells(sheet.LastRow, ColumnList.Count); if (!boolResult) { table.ApplyHorizontalMerge(0, 0, (ColumnList.Count - 1)); } //Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table. for (int r = 1; r <= sheet.LastRow; r++) { for (int c = 1; c <= ColumnList.Count; c++) { int c2 = ColumnList[c - 1]; CellRange xCell = sheet.Range[r, c2]; TableCell wCell = table.Rows[r - 1].Cells[c - 1]; //Fill data to Word table TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); } } //Set column width of Word table in Word for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Rows[i].Cells.Count; j++) { table.Rows[i].Cells[j].Width = 100f; } } //NoBreakTable(table); } else if (ColumnList.Count < 2 * numcol - 1) // 2 part { //Part 1 of 2 title = section1.AddParagraph(); title.Text = item.Name + " Part 1 of 2"; title.ApplyStyle(style.Name); //Write Table table = section1.AddTable(true); table.ResetCells(sheet.LastRow, numcol); if (!boolResult) { table.ApplyHorizontalMerge(0, 0, numcol - 1); } //Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table. for (int r = 1; r <= sheet.LastRow; r++) { for (int c = 1; c <= numcol; c++) { int c2 = ColumnList[c - 1]; CellRange xCell = sheet.Range[r, c2]; TableCell wCell = table.Rows[r - 1].Cells[c - 1]; //Fill data to Word table TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); } } //Set column width of Word table in Word for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Rows[i].Cells.Count; j++) { table.Rows[i].Cells[j].Width = 100f; } } //NoBreakTable(table); //Part 2 of 2 title = section1.AddParagraph(); title.Text = item.Name + " Part 2 of 2"; title.ApplyStyle(style.Name); //Write Table table = section1.AddTable(true); table.ResetCells(sheet.LastRow, ColumnList.Count - numcol + 1); // first column and the rest if (!boolResult) { table.ApplyHorizontalMerge(0, 0, ColumnList.Count - numcol); } //Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table. for (int r = 1; r <= sheet.LastRow; r++) { //for c = 1 CellRange xCell = sheet.Range[r, 1]; TableCell wCell = table.Rows[r - 1].Cells[0]; //Fill data to Word table TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); for (int c = 2; c <= ColumnList.Count - numcol + 1; c++) { int c2 = ColumnList[numcol + c - 2]; xCell = sheet.Range[r, c2]; wCell = table.Rows[r - 1].Cells[c - 1]; //Fill data to Word table textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); } } //Set column width of Word table in Word for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Rows[i].Cells.Count; j++) { table.Rows[i].Cells[j].Width = 100f; } } //NoBreakTable(table); } else // more than 2 part { double value = (double)(ColumnList.Count - numcol) / (numcol - 1); double nf = Math.Ceiling(value) + 1; int n = (int)nf; //Part 1 of n title = section1.AddParagraph(); title.Text = item.Name + $" Part 1 of {n}"; title.ApplyStyle(style.Name); //Write Table table = section1.AddTable(true); table.ResetCells(sheet.LastRow, numcol); if (!boolResult) { table.ApplyHorizontalMerge(0, 0, (numcol - 1)); } //Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table. for (int r = 1; r <= sheet.LastRow; r++) { for (int c = 1; c <= numcol; c++) { int c2 = ColumnList[c - 1]; CellRange xCell = sheet.Range[r, c2]; TableCell wCell = table.Rows[r - 1].Cells[c - 1]; //Fill data to Word table TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); } } //Set column width of Word table in Word for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Rows[i].Cells.Count; j++) { table.Rows[i].Cells[j].Width = 100f; } } //NoBreakTable(table); //Part x of n (2 <= x <= n-1) for (int i = 2; i < n; i++) { title = section1.AddParagraph(); title.Text = item.Name + $" Part {i} of {n}"; title.ApplyStyle(style.Name); //Write Table table = section1.AddTable(true); table.ResetCells(sheet.LastRow, numcol); // first column and the rest if (!boolResult) { table.ApplyHorizontalMerge(0, 0, (numcol - 1)); } //Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table. for (int r = 1; r <= sheet.LastRow; r++) { //for c = 1 CellRange xCell = sheet.Range[r, 1]; TableCell wCell = table.Rows[r - 1].Cells[0]; //Fill data to Word table TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); for (int c = 2; c <= numcol; c++) { int c2 = ColumnList[numcol + (i - 2) * (numcol - 1) + c - 2]; xCell = sheet.Range[r, c2]; wCell = table.Rows[r - 1].Cells[c - 1]; //Fill data to Word table textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); } } //Set column width of Word table in Word for (int it = 0; it < table.Rows.Count; it++) { for (int j = 0; j < table.Rows[it].Cells.Count; j++) { table.Rows[it].Cells[j].Width = 100f; } } //NoBreakTable(table); } //Part n of n title = section1.AddParagraph(); title.Text = item.Name + $" Part {n} of {n}"; title.ApplyStyle(style.Name); //Write Table table = section1.AddTable(true); int lastcol = ColumnList.Count - (numcol + (n - 2) * (numcol - 1)); table.ResetCells(sheet.LastRow, lastcol + 1); // first column and the rest if (!boolResult) { table.ApplyHorizontalMerge(0, 0, lastcol); } //Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table. for (int r = 1; r <= sheet.LastRow; r++) { //for c = 1 CellRange xCell = sheet.Range[r, 1]; TableCell wCell = table.Rows[r - 1].Cells[0]; //Fill data to Word table TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); for (int c = 2; c <= lastcol + 1; c++) { int c2 = ColumnList[numcol + (n - 2) * (numcol - 1) + c - 2]; xCell = sheet.Range[r, c2]; wCell = table.Rows[r - 1].Cells[c - 1]; //Fill data to Word table textRange = wCell.AddParagraph().AppendText(xCell.NumberText); //Copy the formatting of table to Word CopyStyle(textRange, xCell, wCell); } } //Set column width of Word table in Word for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Rows[i].Cells.Count; j++) { table.Rows[i].Cells[j].Width = 100f; } } //NoBreakTable(table); } section1.AddParagraph().Text = ""; //Buffer //continue; break; } iter++; } } }
public int ReadFile(string fileLocation) { string[] files = System.IO.Directory.GetFiles(fileLocation, "*.xlsx"); if (files.Length < 1) { return(0); } var reportingModel = new List <ReportingModel>(); foreach (var file in files) { int weekNumberRow = 5; Workbook workbook = new Workbook(); workbook.LoadFromFile(file); WorksheetsCollection sheets = workbook.Worksheets; foreach (var sheet in sheets) { int startingRow = 9; var sheetName = sheet.CodeName; if (sheet.CodeName.ToLower().Contains("household")) { startingRow = 10; } int columnCount = sheet.Columns.Length; int rowCount = sheet.Rows.Length; for (int start = startingRow; start < rowCount; start++) { var location = sheet.Rows[start].Columns[0].Value; var weekNumber = sheet.Rows[weekNumberRow].Columns[1].Value; for (int c = 1; c < columnCount; c++) { var product = sheet.Rows[startingRow - 1].Columns[c].Value; var sales = sheet.Rows[start].Columns[c].Value; if (!string.IsNullOrWhiteSpace(sales)) { var data = new ReportingModel { Location = location, Week = weekNumber, Product = product,//.Split("|")[0], ProductType = sheetName, Sales = sales }; reportingModel.Add(data); } } } } } try { CreateFile(reportingModel); MoveFile(files); } catch (Exception ex) { LogFile.Write("An Error has accure white creating entries"); } return(reportingModel.Count); }