public static bool ToXls(SqlObjTable data, string fileName, string sheetName = "Report")
{
var wb = XlsWorkbook(data, fileName, sheetName);
if (wb == null)
{
return(false);
}
CultureInfo cultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
bool success = false;
// write Excel BIFF binary stream
try
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
FileStream fs = new FileStream(fileName, FileMode.Create);
wb.Write(fs);
fs.Close();
success = true;
}
catch (Exception ex)
{
Web.Log.WriteException(string.Format("Error saving {0} : {1}", fileName, ex.Message), ex);
}
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
return(success);
}
public static bool Exec(this SqlObjTable tableObj, DbContext context,
string[] param, int iFrom, int iTake)
{
string execName = param[0];
var proc = SqlProcExt.CmdText(execName, context);
var fld = tableObj.Fields;
DataTable table = proc.Result();
#region Head
Array.Resize <SqlField>(ref fld, table.Columns.Count);
tableObj.Fields = fld;
foreach (DataColumn column in table.Columns)
{
int ordinal = column.Ordinal;
tableObj.Fields[ordinal] = new SqlField()
{
Ordinal = ordinal,
Name = column.ColumnName,
Caption = column.Caption,
Type = column.DataType,
Width = 0
};
}
#endregion
var query = table.Rows.Cast <DataRow>().AsQueryable <DataRow>();
if (iTake > 0)
{
query = query.Take(iTake);
}
if (iFrom > 0)
{
query = query.Skip(iFrom);
}
foreach (DataRow row in query)
{
table.Rows.Add(row.ItemArray);
}
return(true);
}
public static NPOI.HSSF.UserModel.HSSFWorkbook XlsWorkbook(SqlObjTable data, string fileName, string sheetName = "Report")
{
CultureInfo cultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
var wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
var ws = wb.CreateSheet(sheetName);
// header columns
var rowHead = ws.CreateRow(0);
foreach (var field in data.Fields)
{
rowHead.CreateCell(field.Ordinal).SetCellValue(field.Caption);
}
// export data
int index = 0;
foreach (var row in data.Rows)
{
int rowNdx = index++; // data.Rows.IndexOf(row);
var rowItem = ws.CreateRow(rowNdx + 1);
foreach (var field in data.Fields)
{
int colNdx = field.Ordinal;
Type type = field.Type;
string value = row[colNdx] as string;
if (type.Equals(typeof(string)) &&
field.Name.StartsWith("html") &&
!string.IsNullOrWhiteSpace(value))
{
var valueBr = value.Replace("<br/>", "\n");
var el = System.Xml.Linq.XElement.Parse(value);
var valueRtf = new StringWriter();
foreach (XElement item in el.Descendants("div"))
{
valueRtf.Write(item.Value.Replace("\n", " \r\n"));
}
rowItem.CreateCell(colNdx).SetCellValue(valueRtf.ToString());
}
else
if (type.Equals(typeof(System.String)))
{
rowItem.CreateCell(colNdx).SetCellValue(value);
}
else
{
double number;
object valueNum = valueNum = row[colNdx];
if (valueNum == null && value == null)
{
rowItem.CreateCell(colNdx).SetCellValue(String.Empty);
}
if (valueNum is int)
{
var cell = rowItem.CreateCell(colNdx, NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue((valueNum as int?).Value);
}
else if (valueNum is double)
{
var cell = rowItem.CreateCell(colNdx, NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue((valueNum as double?).Value);
}
else if (double.TryParse(value, out number))
{
var cell = rowItem.CreateCell(colNdx, NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue(number);
}
else if (value.Length > 0 && value[0] == '\'' && double.TryParse(value.Substring(1), out number))
{
rowItem.CreateCell(colNdx).SetCellValue(value.Substring(1));
}
else
{
// var obj = dt.Rows[rowNdx][colNdx];
var obj = row[colNdx];
rowItem.CreateCell(colNdx).SetCellValue(GetString(obj));
}
}
}
}
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
return(wb);
}
public static bool ToXls(SqlObjTable data, string fileName, string sheetName = "Report")
{
return(NPoi.Excel.ToXls(data, fileName, sheetName));
}