1. Convert DataTable into Excel table, export and save to file
// GET: /Excel/
Models.zbwxglEntities myMdl = new Models.zbwxglEntities();
/// <summary>
/// The first method is to use the file output stream to perform read and write operations.
/// </summary>
public void outExcel(DataTable table)
{
DataTable dtData = table;
string shtnl = "";
shtnl = "<table border='1' cellspacing='1' cellpadding='1'>";
shtnl = shtnl + "<thead>";
for (int j = 0; j < dtData.Columns.Count; j + + )
{
shtnl = shtnl + "<th>" + j + "</th>";
}
shtnl = shtnl + "</thead><tbody>";
for (int i = 0; i < dtData.Rows.Count; i + + )
{
shtnl = shtnl + "<tr>";
for (int j = 0; j < dtData.Columns.Count; j + + )
{
shtnl = shtnl + "<td>" + dtData.Rows[i][j] + "</td>";
}
shtnl = shtnl + "</tr>";
}
shtnl = shtnl + "</tbody></table>";
ExportToExcel("application/x-excel", "1234.xls", shtnl);
}
public void ExportToExcel(string FieldType, string FileName, string dt)
{
System.Web.HttpContext.Current.Response.Charset = "utf-8";
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
System.Web.HttpContext.Current.Response.ContentType = FieldType;
StringWriter tw = new StringWriter();
System.Web.HttpContext.Current.Response.Output.Write(dt);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}
/// <summary>
/// The second method is to use Microsoft’s own plug-in
/// </summary>
/// <returns></returns>
public ActionResult DownloadFile(DataTable table)
{
try
{
DataTable dt = table;
string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");
string str = Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "Content\DownLoadTest\" + Session["YongHuID"] + strdate + "Excel.xls";
if (System.IO.File.Exists(str))
{
//If it exists, delete it
System.IO.File.Delete(str);
}
ConvertHelper myConvertHelper = new ConvertHelper();
DataTableToExcel(dt, str);//Convert DataTable data to Excel data
System.Threading.Thread.Sleep(5000);
return File(str, "application/vnd.ms-excel", strdate + "12345.xls");
}
catch
{
DataTable dt = new DataTable();
List<Dictionary<string, object>> ListReturn = ConvertHelper.DtToList(dt);
return Json(ListReturn, JsonRequestBehavior.AllowGet);
}
}
public void DataTableToExcel(DataTable datas, string p)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.SheetsInNewWorkbook = 1;
app.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)app.ActiveWorkbook.Worksheets[1];
for (int i = 0; i < datas.Columns.Count; i + + )
{
sheet.Cells[1, i + 1] = datas.Columns[i].ColumnName;
}
for (int i = 0; i < datas.Rows.Count; i + + )
{
for (int j = 0; j < datas.Columns.Count; j + + )
{
sheet.Cells[2 + i, j + 1] = datas.Rows[i][j].ToString();
}
}
app.Visible = true;
System.Threading.Thread.Sleep(500);
try
{
app.ActiveWorkbook.SaveAs(p);
}
catch { }
app.Quit();
}
/// <summary>
/// The third method is to use NPOI plug-in
/// </summary>
/// <returns></returns>
public FileResult DownLoadExcelJiZuChaXunGenRenXiaoFeiJiLu(DataTable table)
{
DataTable dt = table;//Get the datatable data that needs to be exported
//Create an Excel file object
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Add a sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//Add the header title of the first row to sheet1
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//row1.RowStyle.FillBackgroundColor = "";
for (int i = 0; i < dt.Columns.Count; i + + )
{
row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//Write data gradually into each row of sheet1
for (int i = 0; i < dt.Rows.Count; i + + )
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j + + )
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
}
}
string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");//Get the current time
//Write to client
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", strdate + "Excel.xls");
}
2. Import Excel data into DataTable
/// <summary>
/// Excel import
/// </summary>
/// <returns></returns>
public ActionResult GetTableFromExcel()
{
//FileStream file = new FileStream(Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "excel\123.xlsx", FileMode.Open, FileAccess.Read);
HttpPostedFileBase fostFile = Request.Files["file1"];
Stream streamfile = fostFile.InputStream;
//HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0))
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//The first row is the header row
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i + + )
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i + + )
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j + + )
{
if (row.GetCell(j) != null)
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
table.Rows.Add(dataRow);
}
for (int i = 0; i < table.Rows.Count; i + + )
{
//myUpLoadBLL.ForDownLoad(table.Rows[i][1].ToString(), table.Rows[i][2].ToString(),Convert.ToBoolean( table.Rows[i][3]));
}
}
return Content("");
}
/// <summary>
/// Get the value of the column based on the Excel column type
/// </summary>
/// <param name="cell">Excel column</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch(cell.CellType)
{
case CellType.BLANK:
return string.Empty;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.ERROR:
return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
return cell.ToString();
case CellType.STRING:
return cell.StringCellValue;
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}