public string GetSurveyXml(HttpPostedFileBase NewFile) { using (var package = new OfficeOpenXml.ExcelPackage(NewFile.InputStream)) { OfficeOpenXml.ExcelWorkbook workbook = package.Workbook; return(SurveyXml.BuildXml(workbook).ToString()); } }
public static OfficeOpenXml.ExcelWorksheet GetWorksheetByIndex(OfficeOpenXml.ExcelPackage package, int index) { try { OfficeOpenXml.ExcelWorkbook workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { return(workBook.Worksheets[index]); } } } catch { } return(null); }
public static OfficeOpenXml.ExcelWorksheet GetWorksheetFirst(OfficeOpenXml.ExcelPackage package) { try { OfficeOpenXml.ExcelWorkbook workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { return(workBook.Worksheets.FirstOrDefault()); } } } catch { } return(null); }
public static OfficeOpenXml.ExcelWorksheet GetWorksheetByName(OfficeOpenXml.ExcelPackage package, string name) { try { OfficeOpenXml.ExcelWorkbook workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { return(workBook.Worksheets[name]); } } } catch { } return(null); }
private byte[] CreateExcelStream() { System.IO.MemoryStream ms = new System.IO.MemoryStream(); using (System.IO.FileStream fs = System.IO.File.OpenRead(System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "../PlanilhaDeEntrada.xlsx")) { using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(fs)) { OfficeOpenXml.ExcelWorkbook excelWorkBook = excelPackage.Workbook; OfficeOpenXml.ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First(); excelWorksheet.Cells[15, 2].Value = "Ou 'B1'"; excelWorksheet.Cells[15, 3].Value = "Ou 'B2'"; excelWorksheet.Cells[15, 4].Value = "Ou 'B3'"; excelWorksheet.Cells[15, 5].Value = "Ou 'B4'"; excelWorksheet.Cells[15, 6].Value = "Ou 'B5'"; excelWorksheet.Cells[15, 7].Value = "Ou 'B6'"; excelPackage.SaveAs(ms); // This is the important part. return(ms.ToArray()); } } }
/// <summary> /// Processes the given XLSX file. /// </summary> /// <param name="file">File path.</param> /// <returns>Processed file.</returns> private ExcelDataImportResultModel ProcessXlsxFile(string file) { DataRow row = null; DataTable tab = null; List <string> r = null; string v = string.Empty; int sheetIndex = 0, sheetOffset = 0; ExcelDataImportResultModel ret = null; List <string> sheets = new List <string>(); OfficeOpenXml.ExcelWorkbook workbook = null; List <List <string> > data = new List <List <string> >(); bool hasData = false, hasFullData = false, hasRowData = false; List <List <List <string> > > fullData = new List <List <List <string> > >(); int maxColumns = 25, maxRows = 25, longestRow = 0, offset = 0, i = 0, j = 0; using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(file))) { workbook = package.Workbook; if (workbook != null && workbook.Worksheets != null && workbook.Worksheets.Any()) { if (workbook.Worksheets.Count > 1) { sheets.AddRange(workbook.Worksheets.Select(s => s.Name)); } foreach (var s in workbook.Worksheets) { hasData = false; data.Clear(); for (i = 1; i <= maxRows; i++) { data.Add(new List <string>()); hasRowData = false; for (j = 1; j <= maxColumns; j++) { v = s.Cells[i, j].GetValue <string>(); if (!hasRowData) { hasRowData = !string.IsNullOrWhiteSpace(v); } if (!hasData) { hasData = hasRowData; } data[data.Count - 1].Add(v); } if (!hasRowData) { data.RemoveAt(data.Count - 1); } else if (data[data.Count - 1].Count > longestRow) { offset = 0; r = data[data.Count - 1]; while (string.IsNullOrWhiteSpace(r[r.Count - offset - 1]) && offset < (r.Count - 1)) { offset++; } if (r.Count - offset > longestRow) { longestRow = r.Count - offset; } } } if (hasData) { for (i = 0; i < data.Count; i++) { if (data[i].Count > longestRow) { data[i].RemoveRange(longestRow, data[i].Count - longestRow); } } hasFullData = true; fullData.Add(new List <List <string> >(data.Select(d => new List <string>(d)))); } else if ((sheetIndex - sheetOffset) >= 0 && (sheetIndex - sheetOffset) < sheets.Count) { sheets.RemoveAt(sheetIndex - sheetOffset); sheetOffset++; } sheetIndex++; } } } if (hasFullData) { ret = new ExcelDataImportResultModel(); ret.AvailableSheets = sheets; foreach (var fd in fullData) { tab = new DataTable(); for (i = 0; i < fd.Count; i++) { row = new DataRow(); for (j = 0; j < fd[i].Count; j++) { row.Cells.Add(new DataCell() { Value = fd[i][j] }); } tab.Rows.Add(row); } ret.SheetData.Add(tab); } } return(ret); }
} // End Function GetCellValueAsString public static System.Data.DataTable ProcessWorkbook() { System.Data.DataTable dt = new System.Data.DataTable(); string fn = @"D:\username\Documents\Visual Studio 2013\Projects\Pdf2Svg\Pdf2Svg\Gebäudepläne.xlsx"; if (System.IO.File.Exists(fn)) { using (System.IO.FileStream fs = new System.IO.FileStream(fn, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read)) { // Get the file we are going to process // Open and read the XlSX file. // System.IO.FileInfo existingFile = new System.IO.FileInfo(fn); // using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(existingFile)) using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(fs)) { // Get the work book in the file OfficeOpenXml.ExcelWorkbook workBook = package.Workbook; if (workBook != null) { // dt = ProcessingFunction(workBook); // OfficeOpenXml.ExcelWorksheet ws = workBook.Worksheets.First(); OfficeOpenXml.ExcelWorksheet roomsWorksheet = workBook.Worksheets["ZeichnungsnamenPDFExport"]; //OfficeOpenXml.ExcelWorksheet UsageTypesWorksheet = workBook.Worksheets["Tabelle1"]; if (roomsWorksheet == null) { return(dt); } // if (roomsWorksheet != null) // UsageTypesWorksheet.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden; int iStartRow = roomsWorksheet.Dimension.Start.Row; int iEndRow = roomsWorksheet.Dimension.End.Row; int iStartColumn = roomsWorksheet.Dimension.Start.Column; int iEndColumn = roomsWorksheet.Dimension.End.Column; // OfficeOpenXml.ExcelRange cell = roomsWorksheet.Cells["A1"]; OfficeOpenXml.ExcelRange cell = null; // ewbGroupPermissionWorksheet.Cells[1, 1]; // Cells[y, x] for (int j = 1; j <= iEndColumn; ++j) { cell = roomsWorksheet.Cells[1, j]; // Cells[y, x] string title = GetCellValueAsString(cell); // if (string.IsNullOrWhiteSpace(title)) continue; dt.Columns.Add(title, typeof(string)); } System.Console.WriteLine(dt.Columns.Count); int ordObjekttyp = dt.Columns["Standort/Geschoss"].Ordinal + 1; int ordZeichnungsname = dt.Columns["Zeichnungsname"].Ordinal + 1; System.Data.DataRow dr = null; // 2 - 348 for (int i = 2; i <= iEndRow; ++i) { dr = dt.NewRow(); for (int j = 1; j <= iEndColumn; ++j) { cell = roomsWorksheet.Cells[i, j]; // Cells[y, x] string cellValue = GetCellValueAsString(cell); dr[j - 1] = cellValue; } // Next j dt.Rows.Add(dr); } // Next i System.Console.WriteLine(dt.Rows.Count); } // End if (workBook != null) } // End Using package } // End Using ms } return(dt); }
private System.Data.DataTable ProcessWorkbook() { System.Data.DataTable dt = new System.Data.DataTable(); byte[] ba = null; bool bDebug = true; string fn = @"D:\ExampleMultitabReport.xlsx"; if (!System.IO.File.Exists(fn) || !bDebug) { // COR_Reports.ReportFormatInfo pdfFormatInfo = new COR_Reports.ReportFormatInfo(COR_Reports.ExportFormat.PDF); // COR_Reports.ReportFormatInfo htmlFormatInfo = new COR_Reports.ReportFormatInfo(COR_Reports.ExportFormat.HtmlFragment); // COR_Reports.ReportFormatInfo excelFormatInfo = new COR_Reports.ReportFormatInfo(COR_Reports.ExportFormat.Excel); COR_Reports.ReportFormatInfo excelOpenXmlFormatInfo = new COR_Reports.ReportFormatInfo(COR_Reports.ExportFormat.ExcelOpenXml); ba = GetFooter("ExampleMultitabReport.rdl", excelOpenXmlFormatInfo); } else { ba = System.IO.File.ReadAllBytes(fn); } using (System.IO.MemoryStream ms = new System.IO.MemoryStream(ba)) { // Get the file we are going to process // Open and read the XlSX file. // System.IO.FileInfo existingFile = new System.IO.FileInfo(fn); // using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(existingFile)) using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(ms)) { // Get the work book in the file OfficeOpenXml.ExcelWorkbook workBook = package.Workbook; if (workBook != null) { // dt = ProcessingFunction(workBook); // OfficeOpenXml.ExcelWorksheet ws = workBook.Worksheets.First(); OfficeOpenXml.ExcelWorksheet roomsWorksheet = workBook.Worksheets["Räume"]; OfficeOpenXml.ExcelWorksheet UsageTypesWorksheet = workBook.Worksheets["Nutzungsarten"]; if (roomsWorksheet == null) { return(dt); } if (roomsWorksheet != null) { UsageTypesWorksheet.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden; } int iStartRow = roomsWorksheet.Dimension.Start.Row; int iEndRow = roomsWorksheet.Dimension.End.Row; int iStartColumn = roomsWorksheet.Dimension.Start.Column; int iEndColumn = roomsWorksheet.Dimension.End.Column; // OfficeOpenXml.ExcelRange cell = roomsWorksheet.Cells["A1"]; OfficeOpenXml.ExcelRange cell = null; // ewbGroupPermissionWorksheet.Cells[1, 1]; // Cells[y, x] for (int j = 1; j <= iEndColumn; ++j) { cell = roomsWorksheet.Cells[3, j]; // Cells[y, x] string title = GetCellValueAsString(cell); // if (string.IsNullOrWhiteSpace(title)) continue; dt.Columns.Add(title, typeof(string)); } // Next j System.Console.WriteLine(dt.Columns.Count); int ord = dt.Columns["NA Lang DE"].Ordinal + 1; // https://stackoverflow.com/questions/29764226/add-list-validation-to-column-except-the-first-two-rows // public static string GetAddress(int FromRow, int FromColumn, int ToRow, int ToColumn) string range = OfficeOpenXml.ExcelRange.GetAddress(4, ord, OfficeOpenXml.ExcelPackage.MaxRows, ord); OfficeOpenXml.DataValidation.Contracts.IExcelDataValidationList val = roomsWorksheet.DataValidations.AddListValidation(range); string char1 = "A"; string char2 = "A"; int num1 = 1; int num2 = 300; // https://stackoverflow.com/questions/20259692/epplus-number-of-drop-down-items-limitation-in-excel-file //val.Formula.ExcelFormula = string.Format("=DropDownWorksheetName!${0}${1}:${2}${3}", char1, num1, char2, num2); val.Formula.ExcelFormula = string.Format("=Nutzungsarten!{0}", "H2:H72"); val.ShowErrorMessage = true; val.Error = "Select a value from list of values ..."; for (int i = 4; i <= iEndRow; ++i) { for (int j = 1; j <= iEndColumn; ++j) { cell = roomsWorksheet.Cells[3, j]; // Cells[y, x] cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.HotPink); // cell.Style.Font.Strike; OfficeOpenXml.Style.ExcelRichText ert = roomsWorksheet.Cells[2, 1].RichText.Add("RichText"); ert.Bold = true; ert.Color = System.Drawing.Color.Red; ert.Italic = true; ert.Size = 12; ert = roomsWorksheet.Cells[2, 1].RichText.Add("Test"); ert.Bold = true; ert.Color = System.Drawing.Color.Purple; ert.Italic = true; ert.UnderLine = true; ert.Strike = true; ert = roomsWorksheet.Cells[2, 1].RichText.Add("123"); ert.Color = System.Drawing.Color.Peru; ert.Italic = false; ert.Bold = false; // Can't add validation when a validation already exists // OfficeOpenXml.DataValidation.Contracts.IExcelDataValidationList cellValidation = cell.DataValidation.AddListDataValidation(); break; // https://stackoverflow.com/questions/9859610/how-to-set-column-type-when-using-epplus // https://stackoverflow.com/questions/22832423/excel-date-format-using-epplus } // Next j break; } // Next i } // End if (workBook != null) using (System.IO.FileStream fs = new System.IO.FileStream(@"D:\ModifiedExcelFile.xlsx", System.IO.FileMode.Create, System.IO.FileAccess.Write, System.IO.FileShare.None)) { package.SaveAs(fs); } // End using fs } // End Using package } // End Using ms return(dt); }
public JsonResult Export(JsExportOptions Options) { byte[] content = null; int startRow = 1, startColumn = 1; JsonResult result = new JsonResult() { JsonRequestBehavior = JsonRequestBehavior.AllowGet }; string id = Guid.NewGuid().ToString(); JsSelectOptions so = Options.SelectOptions; if (Options.Type == ExportTypesEnum.AllRows) { so.Skip = -1; so.Take = -1; } Selecter <ObjectContext> selecter = new Selecter <ObjectContext>(model); JsSelectResult selectResult = selecter.Select(so); IEnumerable <EntityObject> data = selectResult.Collections[so.EntitySetName]; string fileName = Options.Name.StringAndTrim(); fileName += !fileName.EndsWith(".xlsx") ? ".xlsx" : ""; fileName = fileName.Replace(System.IO.Path.GetInvalidFileNameChars(), ""); OfficeOpenXml.ExcelPackage p = new OfficeOpenXml.ExcelPackage(); OfficeOpenXml.ExcelWorkbook book = p.Workbook; OfficeOpenXml.ExcelWorksheet sheet; Type itType; EntityObject first; try { first = data.FirstOrDefault(); itType = first != null?first.GetType() : null; sheet = book.Worksheets.Add(Options.Name); int i = 0; foreach (JsExportParameter ep in Options.Parameters) { ep.Compile(itType); sheet.Cells[startRow, startColumn + i].Value = ep.Name; sheet.Column(startColumn + i).AutoFit(); sheet.Column(startColumn + i).BestFit = true; i++; } if (!data.Any()) { sheet.Row(startRow).Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Row(startRow).Style.Font.Bold = true; using (MemoryStream ms = new MemoryStream()) { p.SaveAs(ms); content = ms.ToArray(); result.Data = new { Success = true, Result = data.Count(), ID = id }; HttpContext.Cache[id] = File(content, XlsxContentType, fileName); return(result); } } i = startRow + 1; foreach (EntityObject item in data) { for (int j = 0; j < Options.Parameters.Length; j++) { JsExportParameter par = Options.Parameters[j]; object value = par.GetValue(item); if (value != null && value.GetType() == typeof(DateTime)) { value = ((DateTime)value).ToShortDateString(); } sheet.Cells[i, startColumn + j].Value = value; } i++; } sheet.Row(startRow).Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Row(startRow).Style.Font.Bold = true; for (i = 0; i < sheet.Dimension.End.Column; i++) { sheet.Column(startColumn + i).AutoFit(); sheet.Column(startColumn + i).BestFit = true; } using (MemoryStream ms = new MemoryStream()) { p.SaveAs(ms); content = ms.ToArray(); result.Data = new { Success = true, Result = new { Count = data.Count(), ID = id }, Code = 200 }; HttpContext.Cache[id] = File(content, XlsxContentType, fileName); } } catch (Exception ex) { result.Data = new { Success = false, Error = ex.Message }; } finally { p.Dispose(); } return(result); }