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