Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, December 2, 2020 10:27 AM
Hello,
I have a form with different text fields and dropdown lists.
after entering the information, I have a button that allows me to validate the creation of this new element.
this button must call an action of my controller which is used to generate an excel file where I must call the template of this file from my local files and display the information entered in the fields that correspond in the excel file
I am using npoi library (c # asp.net MVC)
All replies (4)
Thursday, December 3, 2020 9:33 AM ✅Answered
Hi silbahi,
Based on your description, I wrote an example for your reference.
You can modify it according to actual needs.
Model
public class TestModel
{
public string ID { get; set; }
public string Name { get; set; }
public string RoleName { get; set; }
}
Controller
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
public ActionResult OpentemplateFile()
{
var model = DataTableToObject<TestModel>(ReadWorkbook());
return View("Index", model);
}
public ActionResult GenerateExcel(TestModel model)
{
var datable = ConvertToDatable<TestModel>(model);
HSSFWorkbook excelBook = new HSSFWorkbook(); //Create workbook Excel
ISheet sheet1 = excelBook.CreateSheet("Sheet1");//Create and name the worksheet for the workbook
for (var i = 0; i < datable.Rows.Count; i++)
{
IRow irowname = sheet1.CreateRow(i);
for (int j = 0; j < datable.Columns.Count; j++)
{
irowname.CreateCell(j).SetCellValue(datable.Columns[j].ToString());
}
IRow irow = sheet1.CreateRow(i + 1);
for (int m = 0; m < datable.Rows[i].ItemArray.ToList().Count; m++)
{
irow.CreateCell(m).SetCellValue(datable.Rows[i].ItemArray.ToList()[m].ToString());
}
}
var fileName = "excelfile" + DateTime.Now.ToString() + ".xls";
//Convert the Excel table into a stream and output
MemoryStream bookStream = new MemoryStream();//Create file stream
excelBook.Write(bookStream); //File write stream (write byte sequence to stream)
bookStream.Seek(0, SeekOrigin.Begin);//Call Seek before output, specifying the 0 position as the starting position
return File(bookStream, "application/vnd.ms-excel", fileName);//Finally returned as a file
}
//Convert datable to object
public T DataTableToObject<T>(DataTable datable) where T : class, new()
{
T obj = new T();
foreach (var row in datable.AsEnumerable())
{
foreach (var prop in obj.GetType().GetProperties())
{
PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
}
}
return obj;
}
//Convert object to datable
public DataTable ConvertToDatable<T>(T model)
{
DataTable table = new DataTable(typeof(T).Name);
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(model);
}
table.Rows.Add(values);
return table;
}
//read excel file
public DataTable ReadWorkbook()
{
string path = Server.MapPath("/Test.xlsx");
IWorkbook book = null;
DataTable dataTable = null;
DataColumn column = null;
DataRow dataRow = null;
bool isColumnName = true;
IRow row = null;
ICell cell = null;
int startRow = 0;
try
{
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (Path.GetExtension(Path.GetFileName(path)).ToLower() == ".xls".ToLower())
{
book = new HSSFWorkbook(fs);
}
else
{
book = new XSSFWorkbook(fs);
}
if (book != null)
{
ISheet sheet = book.GetSheetAt(0);
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//Total number of rows
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//first row
int cellCount = firstRow.LastCellNum;//Number of columns
if (isColumnName)
{
startRow = 1;//If the first row is the column name, start reading from the second row
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//fill row
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//Processing of time format (2020.12.3, 2020/12/3, 2020-12-3, etc.)
if (format == 14 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
catch (Exception ex)
{
}
return dataTable;
}
}
View
@{
ViewBag.Title = "Home Page";
}
@model DailyMVCNPOIFDemo.Models.TestModel
@using (Html.BeginForm("OpentemplateFile", "Home"))
{
<button type="submit">Fill in</button>
}
@using (Html.BeginForm("GenerateExcel", "Home", FormMethod.Post))
{
@Html.DisplayNameFor(model => model.ID)
@Html.TextBoxFor(model => model.ID)
@Html.DisplayNameFor(model => model.Name)
@Html.TextBoxFor(model => model.Name)
@Html.DisplayNameFor(model => model.RoleName)
@Html.TextBoxFor(model => model.RoleName)
<button type="submit">submit</button>
}
**Here is the result. **
Best Regards,
YihuiSun
Wednesday, December 2, 2020 3:17 PM
If the file is on the client, then the form should include a input type=file and include the file in the submit.
Friday, December 4, 2020 7:53 AM
the file is in local resources
I have to enter information through an interface
after committing i need to call a controller method action which does the following processing:
fill in the information entered in the excel file (template) from the form and open this file
Thursday, December 10, 2020 5:10 AM
Hi silbahi,
- he file is in local resources
- In the previous example, for the convenience of testing, I put the file under the project, you can modify the path to the path of the local file.
- I have to enter information through an interface
- In the example I provided earlier,I set the default value in the template excel file, so after I click the fill button, the data in the template excel file will be filled into the input, and then the value will be modified as needed.
- If you don't want it, you can delete the relevant code of the fill button.
- fill in the information entered in the excel file (template) from the form and open this file
- The GenerateExcel method is to generate an excel file based on the values entered on the page.
Best Regards,
YihuiSun