private string GetColumnDefinitions(ArrayList cols, ArrayList headerCells, ArrayList dataCells, bool addType)
{
string result = "";
int i = 0;
foreach (ISDWorksheetCell headerCell in headerCells)
{
if (!result.Equals(""))
{
result += ", ";
}
result += "[" + headerCell.Text + "]";
if (addType)
{
ISDWorksheetCell dataCell = (ISDWorksheetCell)dataCells[i];
result += " varchar(" + cols[i] + ")";
}
i++;
}
return(result);
}
public void Save(System.IO.Stream OutputStream, System.Web.HttpResponse response)
{
string filename = "Export_" + Guid.NewGuid().ToString();
string completePathOne = GenerateUniquePath(filename, "xlsx");
string completePathTwo = GenerateUniquePath(filename, "xls");
string completePath = null;
string tableName = null;
ISDWorksheet ws = (ISDWorksheet)this.Worksheets[0];
ISDTable ta = ws.Table;
tableName = ws.Name;
ArrayList rows = ta.Rows;
ISDWorksheetRow row0 = null;
if (rows.Count > 0)
{
row0 = (ISDWorksheetRow)rows[0];
}
ISDWorksheetRow row1 = row0;
if (rows.Count > 1)
{
row1 = (ISDWorksheetRow)rows[1];
}
ArrayList cols = ta.Columns;
string colDefs = GetColumnDefinitions(cols, row0.Cells, row1.Cells, true);
string colNames = GetColumnDefinitions(cols, row0.Cells, row1.Cells, false);
completePath = completePathTwo;
HSSFWorkbook hssfwb = new HSSFWorkbook();
IDataFormat format = hssfwb.CreateDataFormat();
ISheet sh = hssfwb.CreateSheet("Sheet1");
int rIndex = 0;
IRow r = sh.CreateRow(rIndex);
int c = 0;
HSSFCellStyle[] styles = new HSSFCellStyle[row0.Cells.Count + 1];
foreach (ISDWorksheetCell hCell in row0.Cells)
{
HSSFCellStyle style = (HSSFCellStyle)hssfwb.CreateCellStyle();
ICell ce = r.CreateCell(c);
ce.SetCellValue(hCell.Text);
style.WrapText = true;
styles[c] = (HSSFCellStyle)hssfwb.CreateCellStyle();
ce.CellStyle = style;
c += 1;
}
for (rIndex = 1; rIndex <= rows.Count - 1; rIndex++)
{
ISDWorksheetRow currentRow = (ISDWorksheetRow)rows[rIndex];
r = sh.CreateRow(rIndex);
c = 0;
for (int i = 0; i <= currentRow.Cells.Count - 1; i++)
{
//myValue = myValue.Replace("$", "").Replace(",", "")
ICell ce = r.CreateCell(c);
HSSFCellStyle style = styles[i];
ISDWorksheetCell dCell = (ISDWorksheetCell)currentRow.Cells[i];
string formatStr = dCell.Format;
if (dCell.Type == ISDDataType.ISDInteger || dCell.Type == ISDDataType.ISDNumber)
{
ce.SetCellType(CellType.NUMERIC);
if (dCell.Value != null)
{
ce.SetCellValue(Convert.ToDouble(dCell.Value));
}
if (GetBuildInFormat(dCell.Format) > 0)
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
}
else
{
System.Globalization.NumberFormatInfo info = System.Globalization.CultureInfo.CurrentCulture.NumberFormat;
if (string.IsNullOrEmpty(dCell.Format) || dCell.Format == null)
{
formatStr = "##0.00";
}
else if (dCell.Format.Contains("C") || dCell.Format.Contains("c"))
{
formatStr = info.CurrencySymbol + "##0.00";
}
else if (dCell.Format.Contains("P") || dCell.Format.Contains("p"))
{
formatStr = "##0.00" + info.PercentSymbol;
}
else if (dCell.Format.Contains(info.CurrencySymbol) || dCell.Format.Contains(info.PercentSymbol))
{
// use the user given display format
}
else
{
formatStr = "##0.00";
}
style.DataFormat = format.GetFormat(formatStr);
}
}
else if (dCell.Type == ISDDataType.ISDDateTime)
{
if (dCell.Value != null)
{
ce.SetCellType(CellType.NUMERIC);
ce.SetCellValue(Convert.ToDateTime(dCell.Value));
}
if (GetBuildInFormat(dCell.Format) > 0)
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
}
else
{
System.Globalization.DateTimeFormatInfo info = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat;
// convert the date format understood by Excel
// see http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.71).aspx
switch (dCell.Format)
{
case "d":
formatStr = info.ShortDatePattern;
break;
case "D":
formatStr = info.LongDatePattern;
break;
case "t":
formatStr = info.ShortTimePattern;
break;
case "T":
formatStr = info.LongTimePattern;
break;
case "f":
formatStr = info.LongDatePattern + " " + info.ShortTimePattern;
break;
case "F":
formatStr = info.FullDateTimePattern;
break;
case "g":
formatStr = info.ShortDatePattern + " " + info.ShortTimePattern;
break;
case "G":
formatStr = info.ShortDatePattern + " " + info.LongTimePattern;
break;
case "M":
case "m":
formatStr = info.MonthDayPattern;
break;
case "R":
case "r":
formatStr = info.RFC1123Pattern;
break;
case "s":
formatStr = info.SortableDateTimePattern;
break;
case "u":
formatStr = info.UniversalSortableDateTimePattern;
break;
case "U":
formatStr = info.FullDateTimePattern;
break;
case "Y":
case "y":
formatStr = info.YearMonthPattern;
break;
default:
formatStr = info.ShortDatePattern;
break;
}
// some pattern above might return t but this is not recognized by Excel, so remove it
formatStr = formatStr.Replace("t", "");
style.DataFormat = format.GetFormat(formatStr);
}
}
else
{
ce.SetCellType(CellType.STRING);
if (dCell.Value != null)
{
string myValue = dCell.Text;
if (myValue.Length > 255)
{
myValue = myValue.Substring(0, 255);
}
ce.SetCellValue(myValue);
}
if (GetBuildInFormat(dCell.Format) > 0)
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
}
else
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("TEXT");
style.WrapText = true;
}
}
ce.CellStyle = style;
c += 1;
}
}
MemoryStream ms = new MemoryStream();
hssfwb.Write(ms);
string NPOIDownloadFileName = this.Properties.Title;
if (completePath.EndsWith(".xlsx"))
{
NPOIDownloadFileName += ".xlsx";
}
else
{
NPOIDownloadFileName += ".xls";
}
response.ClearHeaders();
response.Clear();
response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
response.Cache.SetMaxAge(new TimeSpan(0));
response.Cache.SetExpires(new DateTime(0));
response.Cache.SetNoServerCaching();
response.AppendHeader("Content-Disposition", ("attachment; filename=\"" + (NPOIDownloadFileName + "\"")));
response.ContentType = "application/vnd.ms-excel";
OutputStream.Write(ms.ToArray(), 0, ms.ToArray().Length);
return;
}
