public string ExportToExcel(string SheetName, string FileName, DataTable DataSource)
{
if (DataSource.Rows.Count == 0)
{
return(string.Empty);
}
if (DataSource.Columns.Contains("RowNum"))
{
DataSource.Columns.Remove("RowNum");
DataSource.AcceptChanges();
}
//creat workbook
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet(SheetName);
//creat header
//first row / rowIndex=0
IRow row;
ICell cell;
row = sheet.CreateRow(0);
for (var i = 0; i <= DataSource.Columns.Count - 1; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(DataSource.Columns[i].ColumnName);
//'Set header Style
var cellStyle = workbook.CreateCellStyle();
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cell.CellStyle = cellStyle;
//'Set column default width
sheet.SetColumnWidth(i, 6000);
}
//'create records
//'datarow index starts from 0
//'excelrow index starts from 1
var rowIndex = 0;
while (rowIndex < DataSource.Rows.Count)
{
row = sheet.CreateRow(rowIndex + 1);
var colIndex = 0;
while (colIndex < DataSource.Columns.Count)
{
cell = row.CreateCell(colIndex);
cell.SetCellValue(string.Format("{0}", DataSource.Rows[rowIndex][colIndex]));
colIndex += 1;
}
rowIndex += 1;
}
if (!Directory.Exists(mExportHelper.DestFolder))
{
Directory.CreateDirectory(mExportHelper.DestFolder);
}
FileName = string.Format("{0}_{1}.xlsx", FileName, DateTime.Now.ToString("yyyyMMddHHmmss"));
var path = string.Format("{0}/{1}", mExportHelper.DestFolder, FileName);
var file = System.IO.File.Create(path);
workbook.Write(file);
file.Close();
//Get prefix
var urlAuthority = HttpContext.Current.Request.Url.Authority;
//TODO: Get the app name and put into the url of the file
var mPrefix = urlAuthority + HttpContext.Current.Request.ApplicationPath;
if (!string.IsNullOrEmpty(AppName) && urlAuthority.ToLower().Contains(AppName.ToLower()))
{
mPrefix = urlAuthority.Substring(0, urlAuthority.IndexOf(AppName) + 6) + HttpContext.Current.Request.ApplicationPath;
}
return(string.Format("Http://{0}/{1}/{2}/{3}",
mPrefix,
ExportHelper.RootPathName,
mExportHelper.CurrentDirectoryName,
FileName));
}
public JSOptions(ViewModel model)
{
Parent = model;
if (string.IsNullOrEmpty(model.Extension) || model.IsCanonical)
{
FileWrongTypeMessage = Resources["FileWrongTypeMessage"];
}
else
{
FileWrongTypeMessage = string.Format(Resources["FileWrongTypeMessage2"], $"<a href=\"/{Parent.Product}/{AppName.ToLower()}\">{AppName}</a>");
}
}
/// <summary>
/// Get the clean version name
/// </summary>
/// <returns></returns>
public string GetCleanAppName()
{
return(AppName.ToLower().Replace("_", "").Replace(".", ""));
}
