public static System.Data.DataTable GetDataTableFromExcelActiveSheet(string fileName, int startRowIndex, int startColumnIndex, bool ignoreBlankRow = true, params string[] columnHeadNames)
{
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
if (sheet == null)
{
return(null);
}
System.Data.DataTable dataTable = new System.Data.DataTable(sheet.SheetName);
for (int i = 0; i < columnHeadNames.Length; i++)
{
dataTable.Columns.Add(columnHeadNames[i]);
}
if (columnHeadNames.Length > 0)
{
for (int rowIndex = startRowIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);
if (row != null)
{
System.Data.DataRow dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);
int dataTableColumnIndex = 0;
for (int columnIndex = startColumnIndex; columnIndex < row.PhysicalNumberOfCells; columnIndex++)
{
if (dataTableColumnIndex >= dataTable.Columns.Count)
{
break;
}
dataRow[dataTableColumnIndex] = GetCellValue(row.GetCell(columnIndex));
dataTableColumnIndex++;
}
}
}
}
return(dataTable);
}
public void FillObject(object obj, NPOI.SS.UserModel.IRow row)
{
var props = obj.GetType().GetProperties();
var header = row.Sheet.GetRow(0);
foreach (var i in props)
{
var idx = FindText(header, i.Name);
if (idx == -1)
{
continue;
}
var cell = row.GetCell(idx);
bool doSet;
var value = Cell2Object(cell, i.PropertyType, out doSet);
if (doSet)
{
i.SetValue(obj, value);
}
}
}
public static string GetCellString(this NPOI.SS.UserModel.IRow row, Dictionary <string, int> headerCellString_ColumnIndex_Dict, string headerKey)
{
var index = headerCellString_ColumnIndex_Dict[headerKey];
var cell = row.GetCell(index);
if (cell == null)
{
return("");
}
if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
return("");
}
if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
return(cell.StringCellValue);
}
if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
return(cell.StringCellValue);
}
return(cell.ToString());
}
/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
NPOI.SS.UserModel.ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
if (!File.Exists(fileName))
{
return(null);
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//根据文件流创建excel数据结构
NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
//IWorkbook workbook = new HSSFWorkbook(fs);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
public static void ImportExcel2DGV(DataGridView dgv)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "选择待导入的数据文件";
ofd.Filter = "Excel Workbook 97-2003|*.xls|Excel Workbook|*.xlsx";
string filePath;
if (ofd.ShowDialog() == DialogResult.OK)
{
filePath = Path.GetFullPath(ofd.FileName);
FileStream fs;
try
{
fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book =
new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
for (int index = 0; index < sheetCount; index++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(index);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum || lastCellNum - firstCellNum < 2)
{
continue;
}
//MainForm.dataBindings.Clear(); //清空表中数据
//确定第一行是否是数据行
int count = 0; //记录表中数据字段的总数
int start_row = 0;
for (int i = firstCellNum; i < lastCellNum; i++)
{
//if(row.GetCell(i) != null)
//{
//row.GetCell(i).SetCellType(NPOI.SS.UserModel.CellType.String);
if (GPA_CALC.isNum(row.GetCell(i).StringCellValue))
{
count++;
}
//}
}
if (count == 0)
{
start_row = 1;
}
for (int i = start_row; i <= sheet.LastRowNum; i++)
{
string courseName = sheet.GetRow(i).Cells[0].StringCellValue;
//MessageBox.Show(sheet.GetRow(i).Cells.Count.ToString());
double credit;
double grade;
try
{
sheet.GetRow(i).Cells[1].SetCellType(NPOI.SS.UserModel.CellType.String);
sheet.GetRow(i).Cells[2].SetCellType(NPOI.SS.UserModel.CellType.String);
credit = Convert.ToDouble(sheet.GetRow(i).Cells[1].StringCellValue);
grade = Convert.ToDouble(sheet.GetRow(i).Cells[2].StringCellValue);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
credit = 0;
grade = 0;
}
//MessageBox.Show(sheet.LastRowNum.ToString());
InfoVo info = new InfoVo()
{
CourseName = courseName, Credit = credit, Grade = grade
};
//原创的处理InvalidOperationException的方法，当选中新行时会触发使得dataBinding添加异常的一行
if (dgv.CurrentRow != null && dgv.CurrentRow.IsNewRow)//别缺少前面的条件了，否则NRE
{
MainForm.dataBindings.RemoveAt(MainForm.dataBindings.Count - 1);
}
//dgv.ClearSelection();
MainForm.dataBindings.Add(info);
}
}
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
// https://localhost:44397/Blog/OnPostImport
// https://github.com/miladsoft/npoi/blob/master/npoi_Example/Controllers/HomeController.cs
public ActionResult OnPostImport()
{
// using NPOI.HSSF.UserModel;
// using NPOI.SS.UserModel;
// using NPOI.XSSF.UserModel;
// Microsoft.AspNetCore.Http.IFormFile file = Request.Form.Files[0];
string fullPath = @"C:\Users\Administrator\Downloads\demo2.xlsx";
System.IO.Stream baseStream = System.IO.File.OpenRead(@"C:\Users\Administrator\Downloads\demo.xlsx");
Microsoft.AspNetCore.Http.IFormFile file = new Microsoft.AspNetCore.Http.FormFile(baseStream, 0, baseStream.Length, "thePostedFile", "demo.xlsx");
// string folderName = "Upload";
// string webRootPath = "_hostingEnvironment.WebRootPath";
// string newPath = System.IO.Path.Combine(webRootPath, folderName);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
// if (!System.IO.Directory.Exists(newPath))
// {
// System.IO.Directory.CreateDirectory(newPath);
// }
if (file.Length > 0)
{
string sFileExtension = System.IO.Path.GetExtension(file.FileName).ToLower();
NPOI.SS.UserModel.IWorkbook workbook;
NPOI.SS.UserModel.ISheet sheet;
// string fullPath = System.IO.Path.Combine(newPath, file.FileName);
using (var stream = new System.IO.FileStream(fullPath, System.IO.FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
// This will read the Excel 97-2000 formats
// NPOI.HSSF.UserModel.HSSFWorkbook hssfwb = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
}
else
{
// This will read 2007 Excel format
// NPOI.XSSF.UserModel.XSSFWorkbook hssfwb = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
}
sheet = workbook.GetSheetAt(0); //get first sheet from workbook
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
sb.Append("<table class='table'><tr>");
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
sb.Append("<th>" + cell.ToString() + "</th>");
}
sb.Append("</tr>");
sb.AppendLine("<tr>");
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == NPOI.SS.UserModel.CellType.Blank))
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
sb.Append("<td>" + row.GetCell(j).ToString() + "</td>");
}
}
sb.AppendLine("</tr>");
}
sb.Append("</table>");
}
}
return(this.Content(sb.ToString()));
}
/// <summary>
/// 获取单元格，如果不存在则创建
/// </summary>
/// <param name="row">行</param>
/// <param name="columnIndex">单元格索引</param>
/// <returns></returns>
private NPOI.SS.UserModel.ICell GetOrCreateCell(NPOI.SS.UserModel.IRow row, int columnIndex)
{
return(row.GetCell(columnIndex) ?? row.CreateCell(columnIndex));
}
public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int sheetNumber, int startColumnNumber, int startRowNumber, params string[] columnNames)
{
System.Data.DataTable dataSource = new System.Data.DataTable();
if (columnNames.Length < 1)
{
return(dataSource);
}
foreach (string columnName in columnNames)
{
dataSource.Columns.Add(columnName);
}
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(dataSource);
}
if (sheetNumber > workbook.NumberOfSheets)
{
return(dataSource);
}
int sheetIndex = sheetNumber > 0 ? sheetNumber - 1 : workbook.ActiveSheetIndex;
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(sheetIndex);
int startRowIndex = startRowNumber > 0 ? startRowNumber - 1 : 0;
for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
{
System.Data.DataRow dataSourceRow = dataSource.NewRow();
dataSource.Rows.Add(dataSourceRow);
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row != null)
{
int startColumnIndex = startColumnNumber > 0 ? startColumnNumber - 1 : 0;
int columnIndex = 0;
Enumerable.Range(startColumnIndex, dataSource.Columns.Count).ToList().ForEach(cellIndex =>
{
NPOI.SS.UserModel.ICell cell = row.GetCell(cellIndex);
if (cell != null)
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
dataSourceRow[columnIndex] = "";
break;
case NPOI.SS.UserModel.CellType.Boolean:
dataSourceRow[columnIndex] = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
dataSourceRow[columnIndex] = cell.ErrorCellValue;
break;
case NPOI.SS.UserModel.CellType.Formula:
dataSourceRow[columnIndex] = cell.NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
{
dataSourceRow[columnIndex] = cell.DateCellValue;
}
else
{
dataSourceRow[columnIndex] = cell.NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.String:
dataSourceRow[columnIndex] = cell.StringCellValue;
break;
default:
dataSourceRow[columnIndex] = cell.ToString();
break;
}
}
columnIndex++;
});
}
}
return(dataSource);
}
private void sb_import_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = null;
OpenFileDialog sflg = new OpenFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
string st_name = book.GetSheetName(sheetIndex);
XtraTabPage xinka = new XtraTabPage();
xinka.Name = "xin";
xinka.Text = st_name;
NepCalaTable xintab = new NepCalaTable();
xintab.Dock = DockStyle.Fill;
xinka.Controls.Add(xintab);
this.xtraTabControl1.TabPages.Add(xinka);
this.xtraTabControl1.SelectedTabPage = xinka;
this.active_nepCalaTable = xintab;
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
dt.Columns.Add("bushe_xianshu", typeof(int));
//MessageBox.Show(dt.Columns["bushe_xianshu"].DataType.ToString());
dt.Columns.Add("bushe_daoshu", typeof(int));
dt.Columns.Add("bushe_zongdaoshu", typeof(int));
dt.Columns.Add("banqian_daoshu", typeof(int));
dt.Columns.Add("ke_caiji", typeof(int));
dt.Columns.Add("banjia_daoshu", typeof(int));
dt.Columns.Add("hengxiangchang", typeof(int));
dt.Columns.Add("zongxiangchang", typeof(int));
dt.Columns.Add("zonghengbi", typeof(double));
dt.Columns.Add("paodaobi", typeof(double));
lastCellNum = 10;
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
NPOI.SS.UserModel.IRow row0 = sheet.GetRow(0);
this.active_nepCalaTable.jifa_dianju.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(1);
this.active_nepCalaTable.jieshou_dianju.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(2);
this.active_nepCalaTable.jieshou_dianshu.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(3);
this.active_nepCalaTable.jifa_xianju.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(4);
this.active_nepCalaTable.jieshou_xianju.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(5);
this.active_nepCalaTable.muban_zong.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(6);
this.active_nepCalaTable.mobanpao.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(7);
this.active_nepCalaTable.jieshou_xianshu.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(8);
this.active_nepCalaTable.muban_heng.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(9);
this.active_nepCalaTable.bushe_jieshouxianshu.Text = row0.GetCell(13).StringCellValue;
row0 = sheet.GetRow(10);
this.active_nepCalaTable.bushe_jifaxianshu.Text = row0.GetCell(13).StringCellValue;
ds.Tables.Add(dt);
this.active_nepCalaTable.DDT = dt;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
/// <author>范永坚 2017-08-09</author>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
NPOI.SS.UserModel.ISheet sheet = null;
var data = new DataTable();
data.TableName = sheetName;
int startRow = 0;
try
{
sheet = sheetName != null?_workbook.GetSheet(sheetName) : _workbook.GetSheetAt(0);
if (sheet != null)
{
var firstRow = sheet.GetRow(0);
if (firstRow == null)
{
return(data);
}
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
startRow = isFirstRowColumn ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
//.StringCellValue;
var column = new DataColumn(Convert.ToChar(((int)'A') + i).ToString());
if (isFirstRowColumn)
{
var columnName = firstRow.GetCell(i).StringCellValue;
column = new DataColumn(columnName);
}
data.Columns.Add(column);
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j, NPOI.SS.UserModel.MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString();
}
}
data.Rows.Add(dataRow);
}
}
else
{
throw new Exception("Don not have This Sheet");
}
return(data);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
private void import_bt_Click(object sender, EventArgs e)
{
//string filepath = "";
//OpenFileDialog opf = new OpenFileDialog();
//if (opf.ShowDialog() == DialogResult.OK)
//{
// filepath = opf.FileName;
//}
DataSet ds = new DataSet();
System.Data.DataTable dt = null;
OpenFileDialog sflg = new OpenFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
string st_name = book.GetSheetName(sheetIndex);
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new System.Data.DataTable(sheet.SheetName);
dt.Columns.Add("PN", typeof(string));
//MessageBox.Show(dt.Columns["bushe_xianshu"].DataType.ToString());
//dt.Columns.Add("", typeof(int));
dt.Columns.Add("name", typeof(string));
dt.Columns.Add("jobnum", typeof(string));
dt.Columns.Add("ARef", typeof(string));
dt.Columns.Add("size", typeof(string));
dt.Columns.Add("sm", typeof(string));
dt.Columns.Add("Barcode", typeof(string));
lastCellNum = 7;
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
NPOI.SS.UserModel.IRow row0 = sheet.GetRow(0);
ds.Tables.Add(dt);
main_gc.DataSource = ds.Tables[0];
}
for (int i = 0; i < gridView1.RowCount; i++)
{
string LJH = gridView1.GetRowCellValue(i, "PN").ToString();
string mc = gridView1.GetRowCellValue(i, "name").ToString();
string gdh = gridView1.GetRowCellValue(i, "jobnum").ToString();
string BH = gridView1.GetRowCellValue(i, "ARef").ToString();
string cc = gridView1.GetRowCellValue(i, "size").ToString();
string dsm = gridView1.GetRowCellValue(i, "sm").ToString();
string tm = gridView1.GetRowCellValue(i, "Barcode").ToString();
Maticsoft.BLL.parts use = new Maticsoft.BLL.parts();
Maticsoft.Model.parts us = new parts()
{
PN = LJH,
//name = mc,
//jobnum = gdh,
//ARef = BH,
//size = cc,
//sm = dsm,
Barcode = tm,
};
use.Add(us);
}
DevExpress.XtraEditors.XtraMessageBox.Show("导入成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//MessageBox.Show("已成功导入");
Maticsoft.BLL.parts pr = new Maticsoft.BLL.parts();
DataSet ds2 = pr.GetAllList();
main_gc.DataSource = ds2.Tables[0];
}
//public static System.Data.DataTable GetDataTableFromSheet(NPOI.SS.UserModel.ISheet sheet, bool ignoreBlankRow, int startRowIndex, int startColumnIndex, int columnCount, bool firstRowIsColumnHead)
//{
// if (sheet == null) { return null; }
// System.Data.DataTable dataTable = new System.Data.DataTable(sheet.SheetName);
// NPOI.SS.UserModel.IRow row = null;
//}
#endregion
#region 从Sheet中获取DataTable
public static System.Data.DataTable GetDataTableFromSheet(NPOI.SS.UserModel.ISheet sheet, int startRowIndex, int startColumnIndex, bool firstRowIsColumnHead, bool autoAddColumn, bool ignoreBlankRow)
{
if (sheet == null)
{
return(null);
}
System.Data.DataTable dataTable = new System.Data.DataTable(sheet.SheetName);
NPOI.SS.UserModel.IRow row = null;
if (firstRowIsColumnHead)
{
row = sheet.GetRow(0);
}
else
{
row = sheet.GetRow(startRowIndex);
}
if (row != null)
{
for (int i = startColumnIndex; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dataTable.Columns.Add();
}
else
{
if (firstRowIsColumnHead)
{
dataTable.Columns.Add(cell.ToString());
}
else
{
dataTable.Columns.Add();
}
}
}
}
if (startRowIndex == 0 && firstRowIsColumnHead)
{
startRowIndex = 1;
}
for (int rowIndex = startRowIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
{
row = sheet.GetRow(rowIndex);
if (row != null)
{
System.Data.DataRow dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);
int dataTableColumnIndex = 0;
for (int columnIndex = startColumnIndex; columnIndex < row.PhysicalNumberOfCells; columnIndex++)
{
if (dataTableColumnIndex >= dataTable.Columns.Count)
{
if (autoAddColumn)
{
dataTable.Columns.Add();
}
else
{
break;
}
}
dataRow[dataTableColumnIndex] = GetCellValue(row.GetCell(columnIndex));
dataTableColumnIndex++;
}
}
}
return(dataTable);
}
public List <User> ReadExcel()
{
List <User> list = new List <User>();
//读取文件
using (FileStream stream = new FileStream(Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString()) + "/UserInfo.xlsx", FileMode.Open))
{
//创建workbook
//HSSFWorkbook workbook = new HSSFWorkbook(stream);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
//读取sheet
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
//读取数据
int rowIndex = 1;
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex++);
while (row != null)
{
//读取一行中的对象
User u = new User();
if (row.GetCell(0) != null)
{
u.id = (int)row.GetCell(0).NumericCellValue;
}
if (row.GetCell(1) != null)
{
row.GetCell(1).SetCellType(NPOI.SS.UserModel.CellType.String);
u.name = row.GetCell(1).StringCellValue;
}
if (row.GetCell(2) != null)
{
u.age = (int)row.GetCell(2).NumericCellValue;
}
if (row.GetCell(3) != null)
{
row.GetCell(3).SetCellType(NPOI.SS.UserModel.CellType.String);
u.gender = row.GetCell(3).StringCellValue;
}
if (row.GetCell(4) != null)
{
row.GetCell(4).SetCellType(NPOI.SS.UserModel.CellType.String);
u.nationality = row.GetCell(4).StringCellValue;
}
if (row.GetCell(5) != null)
{
row.GetCell(5).SetCellType(NPOI.SS.UserModel.CellType.String);
u.phone = row.GetCell(5).StringCellValue;
}
if (row.GetCell(6) != null)
{
row.GetCell(6).SetCellType(NPOI.SS.UserModel.CellType.String);
u.address = row.GetCell(6).StringCellValue;
}
list.Add(u);
row = sheet.GetRow(rowIndex++);
}
return(list);
}
}
/// <summary>
/// 读取指定Excel指定表数据
/// </summary>
/// <param name="fileName">文件名称</param>
/// <param name="sheet">表格序号</param>
/// <param name="firstTitle">表格第一行是否做列名</param>
/// <returns></returns>
public static System.Data.DataTable Read(string fileName, int sheet, bool firstTitle)
{
System.Data.DataTable result = new System.Data.DataTable();
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
NPOI.SS.UserModel.IWorkbook work = null;
if (fileName.EndsWith("xlsx") || fileName.EndsWith("xlsm"))
{
work = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
}
else
{
work = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
NPOI.SS.UserModel.ISheet hs = work.GetSheetAt(sheet);
System.Collections.IEnumerator rows = hs.GetEnumerator();
NPOI.SS.UserModel.ICell cell;
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current;
if (result.Columns.Count <= 0)
{
if (firstTitle)
{
for (int i = 0; i < row.LastCellNum; i++)
{
cell = row.GetCell(i);
result.Columns.Add(string.Format("column{0}", i));
if (cell != null)
{
result.Columns[i].Caption = cell.ToString();
}
}
continue;
}
else
{
for (int i = 0; i < hs.GetRow(0).LastCellNum; i++)
{
result.Columns.Add(string.Format("column{0}", i));
}
}
}
System.Data.DataRow dr = result.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
cell = row.GetCell(i);
if (cell != null)
{
dr[i] = cell.ToString();
}
else
{
dr[i] = null;
}
}
result.Rows.Add(dr);
}
work = null;
}
}
catch { }
return(result);
}
/// <summary>
/// 读取excel
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private List <System.Data.DataTable> LoadXlsx(string path)
{
var dts = new List <System.Data.DataTable>();
System.Data.DataTable dt = null;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
int sheetCount = 0;
XSSFWorkbook book;
try
{
book = new XSSFWorkbook(fs);
}
catch (Exception ex) {
throw new UserFriendlyException((int)ErrorCode.DataAccessErr, "打开excel文件失败，格式不被支持。");
}
sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = 0;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new System.Data.DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
if (row.GetCell(i) == null)
{
dt.Columns.Add();
continue;
}
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 0; i <= sheet.LastRowNum; i++)
{
System.Data.DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
if (sheet.GetRow(i) == null)
{
continue;
}
if (sheet.GetRow(i).GetCell(j) == null)
{
newRow[j] = "";
}
else
{
var cell = sheet.GetRow(i).GetCell(j);
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
newRow[j] = "";
break;
case NPOI.SS.UserModel.CellType.Boolean:
newRow[j] = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
break;
case NPOI.SS.UserModel.CellType.Formula:
newRow[j] = cell.NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型
{
newRow[j] = cell.DateCellValue;
}
else //其他数字类型
{
newRow[j] = cell.NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.String:
newRow[j] = cell.StringCellValue;
break;
case NPOI.SS.UserModel.CellType.Unknown:
break;
}
}
}
}
dts.Add(dt);
}
return(dts);
}
/// <summary>
/// 从NPOI.SS.UserModel.ISheet中获取数据表。
/// </summary>
/// <param name="iSheet">NPOI.SS.UserModel.ISheet对象。</param>
/// <param name="firstRowIsColumnHead">是否将首行作为标题行。</param>
/// <param name="maxColumnCount">最大列数，如果为0则不限制；如果大于0，则限制为指定的列数。</param>
/// <param name="startColumnIndex">起始列的索引，从0开始。</param>
/// <param name="startRowIndex">起始行的索引，从0开始。</param>
/// <returns>System.Data.DataTable对象。</returns>
public static System.Data.DataTable GetDataTable(NPOI.SS.UserModel.ISheet iSheet, bool firstRowIsColumnHead, int maxColumnCount, int startColumnIndex, int startRowIndex)
{
// 如果为空直接返回空。
if (iSheet == null)
{
return(null);
}
// 获取最大列数和最大行数。
int usedColumnCount = Extension.NPOIMethod.GetUsedColumnCount(iSheet);
int usedRowCount = iSheet.LastRowNum + 1;
// 初始化DataTable。
System.Data.DataTable dataTable = new System.Data.DataTable(iSheet.SheetName);
#region 设置列标题
// 声明标题行。
NPOI.SS.UserModel.IRow headRow = null;
// 如果首行为标题行，则获取首行为标题行。
if (firstRowIsColumnHead)
{
headRow = iSheet.GetRow(0);
}
// 初始化DataTable最大列数为设定的最大列数。
int maxDataTableColumnCount = maxColumnCount;
// 如果未设定最大列数。
if (maxColumnCount <= 0)
{
// 如果标题行为空，DataTable的最大列数为Sheet的最大列数-起始列的索引。
if (headRow == null)
{
maxDataTableColumnCount = usedColumnCount - startColumnIndex;
}
else // 否则，DataTable的最大列数为标题行的列数-起始列的索引。
{
maxDataTableColumnCount = headRow.LastCellNum - startColumnIndex;
}
}
// 创建DataTable数据列。
for (int dataTableColumnIndex = 0; dataTableColumnIndex < maxDataTableColumnCount; dataTableColumnIndex++)
{
// 如果标题行为空，则添加默认数据列。
if (headRow == null)
{
dataTable.Columns.Add("", typeof(object));
}
else // 如果标题行不为空。
{
// 从设定的起始列开始获取标题行的单元格。
NPOI.SS.UserModel.ICell iCell = headRow.GetCell(startColumnIndex + dataTableColumnIndex);
// 如果单元格为空，则添加默认数据列。
if (iCell == null)
{
dataTable.Columns.Add("", typeof(object));
}
else // 如果不为空，则添加名称为单元格值的数据列。
{
dataTable.Columns.Add(iCell.ToString(), typeof(object));
}
}
}
#endregion
#region 读取数据
// 如果起始行索引小于0，且首行最为标题行，则起始行的索引设置为1。
if (startRowIndex <= 0 && firstRowIsColumnHead)
{
startRowIndex = 1;
}
// 循环读取Sheet中所有的数据行。
for (int dataTableRowIndex = startRowIndex; dataTableRowIndex < usedRowCount; dataTableRowIndex++)
{
// 初始化DataTable数据行，并添加到DataTable。
System.Data.DataRow dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);
// 初始化Sheet中的数据行。
NPOI.SS.UserModel.IRow iRow = iSheet.GetRow(dataTableRowIndex);
// 如果Sheet中的数据行不为空。
if (iRow != null)
{
// 初始化Sheet中的单元格。
NPOI.SS.UserModel.ICell iCell = null;
// 循环DataRow中的每一列。
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
// 获取Sheet中的单元格，从设定的起始列开始
iCell = iRow.GetCell(startColumnIndex + dataTableColumnIndex);
// 如果Sheet中的单元格不为空，则将单元格的值赋给DataTable数据行对应的列。
if (iCell != null)
{
dataRow[dataTableColumnIndex] = Extension.NPOIMethod.GetCellValue(iCell);
}
}
}
}
#endregion
return(dataTable);
}
public override void DoLoginedHandlerWork(HttpContext context)
{
Message jsonMessage;
jsonMessage = new Message()
{
Result = false,
TxtMessage = "权限验证失败，可能原因:\n1、数据中心通讯失败。\n2、系统管理员未与您分配对应操作权限。"
};
//获取操作类型AType:ADD,EDIT,DELETE
string AjaxType = context.Request.QueryString["AType"] == null ? string.Empty : context.Request.QueryString["AType"].ToString().ToUpper();
WCFServiceProxy <IUserManage> proxy = null;
try
{
proxy = new WCFServiceProxy <IUserManage>();
if (AjaxType == "UPLOAD")
{
if (context.Request.Files != null && context.Request.Files.Count == 1)
{
HttpPostedFile postedFile = context.Request.Files[0];
DataTable dt = null;
Message m;
List <IoT_UserTemp> list = new List <IoT_UserTemp>();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(postedFile.InputStream);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.NewRow();
for (int j = firstCellNum; j < lastCellNum; j++)
{
NPOI.SS.UserModel.ICell cell = sheet.GetRow(i).GetCell(j);
if (cell == null)
{
newRow[j] = "";
}
else
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
case NPOI.SS.UserModel.CellType.Unknown:
newRow[j] = "";
break;
case NPOI.SS.UserModel.CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
newRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
newRow[j] = cell.NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.String:
newRow[j] = cell.StringCellValue;
break;
case NPOI.SS.UserModel.CellType.Formula:
newRow[j] = cell.CellFormula;
break;
case NPOI.SS.UserModel.CellType.Boolean:
newRow[j] = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
newRow[j] = "";
break;
default:
newRow[j] = "";
break;
}
}
}
if (lastCellNum < 8)
{
continue;
}
decimal meterNum = decimal.TryParse(newRow[3].ToString(), out meterNum) ? meterNum : 0;
IoT_UserTemp gas = new IoT_UserTemp()
{
UserName = newRow[0].ToString(),
UserID = newRow[1].ToString(),
MeterNo = newRow[2].ToString(),
MeterNum = meterNum,
Street = newRow[4].ToString(),
Community = newRow[5].ToString(),
Door = newRow[6].ToString(),
Address = newRow[7].ToString(),
CompanyID = loginOperator.CompanyID
};
/*
* Direction = newRow[8].ToString(),
* InstallType = newRow[9].ToString(),
* Phone = newRow[10].ToString(),
* UserType = newRow[11].ToString() =="0"?"0":"1",
* InstallDate = newRow[12].ToString(), */
if (lastCellNum >= 9)
{
gas.Direction = newRow[8].ToString();
}
if (lastCellNum >= 10)
{
gas.InstallType = newRow[9].ToString();
}
if (lastCellNum >= 11)
{
gas.Phone = newRow[10].ToString();
}
if (lastCellNum >= 12)
{
gas.UserType = newRow[11].ToString();
}
if (lastCellNum >= 13)
{
try
{
gas.InstallDate = Convert.ToDateTime(newRow[12].ToString()).ToString("yyyy-MM-dd");
}
catch { }
}
m = proxy.getChannel.AddTemp(gas);
if (!m.Result)
{
list.Add(gas);
}
}
}
jsonMessage = new Message()
{
Result = true,
TxtMessage = Newtonsoft.Json.JsonConvert.SerializeObject(list)
};
}
}
}
catch (Exception ex) {
jsonMessage = new Message()
{
Result = false,
TxtMessage = ex.Message
};
}
finally
{
proxy.CloseChannel();
}
context.Response.Write(JSon.TToJson <Message>(jsonMessage));
}
public ActionResult Import(VModel.SyUserTeacher.FormImport m)
{
if (ModelState.IsValid)
{
if (m.File.FileName.ToLower().IndexOf("xls") == -1)
{
ModelState.AddModelError("File", string.Format("导入文件必须为Excel文件"));
return(View(m));
}
NPOI.SS.UserModel.IWorkbook book = NPOI.SS.UserModel.WorkbookFactory.Create(m.File.InputStream);
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);
if (sheet != null)
{
List <VModel.SyUserTeacher.ImportData> list = new List <VModel.SyUserTeacher.ImportData>();
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
int startRow = sheet.FirstRowNum + 2;
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
if (row.GetCell(0) == null || string.IsNullOrWhiteSpace(row.GetCell(0).ToString()))
{
ModelState.AddModelError("File", string.Format("第{0}行“用户名”不能为空", i + 1));
return(View(m));
}
else if (Bll.SyTeacherBll.IsExist(row.GetCell(0).ToString(), null))
{
ModelState.AddModelError("File", string.Format("第{0}行“用户名”已经存在", i + 1));
return(View(m));
}
else if (row.GetCell(0).ToString().Length > 20)
{
ModelState.AddModelError("File", string.Format("第{0}行“用户名”最大长度不超过20个字符的内容", i + 1));
return(View(m));
}
else if (row.GetCell(1) == null || string.IsNullOrWhiteSpace(row.GetCell(1).ToString()))
{
ModelState.AddModelError("File", string.Format("第{0}行“姓名”不能为空", i + 1));
return(View(m));
}
//else if (Bll.SyTeacherBll.IsExist(null,row.GetCell(1).ToString()))
//{
// ModelState.AddModelError("File", string.Format("第{0}行“姓名”已经存在", i + 1));
// return View(m);
//}
else if (row.GetCell(1).ToString().Length > 20)
{
ModelState.AddModelError("File", string.Format("第{0}行“姓名”最大长度不超过20个字符的内容", i + 1));
return(View(m));
}
else if (row.GetCell(2) == null || string.IsNullOrWhiteSpace(row.GetCell(2).ToString()))
{
ModelState.AddModelError("File", string.Format("第{0}行“学科”不能为空", i + 1));
return(View(m));
}
else if (Bll.DictSubjectBll.IsExist(row.GetCell(2).ToString()) == false)
{
ModelState.AddModelError("File", string.Format("第{0}行“学科”不存在", i + 1));
return(View(m));
}
VModel.SyUserTeacher.ImportData data = new VModel.SyUserTeacher.ImportData();
data.Account = row.GetCell(0).ToString();
data.Name = row.GetCell(1).ToString();
data.SubjectName = row.GetCell(2).ToString();
list.Add(data);
}
int r = Bll.SyUserBll.ImportTeacher(list);
switch (r)
{
case 200:
return(RedirectToAction("Index"));
}
}
}
return(View(m));
}
public String ExportProductsList([FromUri] String id = "")
{
String url = Request.RequestUri.ToString();
String fileUrl = "";
List <Product> productsList = new List <Product>();
if (id.Trim().Length == 0)
{
productsList = getFullList();
fileUrl = url.Replace("api/ExportProductsList/", "templates/");
url = url.Replace("api/ExportProductsList/", "Content/imgs/");
}
else
{
productsList = getFilteredList(id);
fileUrl = url.Replace("api/ExportProductsList/" + id, "templates/");
url = url.Replace("api/ExportProductsList/" + id, "Content/imgs/");
}
String filename = DateTime.Now.ToString("yyyyMMddHHmmss") + "_products.xls";
String filepath = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/templates"), filename);
String filepathtemplate = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/templates"), "template.xls");
fileUrl = fileUrl + filename;
var fs = new FileStream(filepathtemplate, FileMode.Open, FileAccess.Read);
var templateWorkbook = new HSSFWorkbook(fs, true);
NPOI.SS.UserModel.ISheet sheet = templateWorkbook.GetSheet("ProductsList");
int i = 1;
foreach (Product product in productsList)
{
NPOI.SS.UserModel.IRow dataRow = sheet.GetRow(i);
NPOI.SS.UserModel.ICell cell0 = dataRow.GetCell(0);
cell0.SetCellType(NPOI.SS.UserModel.CellType.String);
cell0.SetCellValue(product.Id);
NPOI.SS.UserModel.ICell cell1 = dataRow.GetCell(1);
cell1.SetCellType(NPOI.SS.UserModel.CellType.String);
cell1.SetCellValue(product.Code);
NPOI.SS.UserModel.ICell cell2 = dataRow.GetCell(2);
cell2.SetCellType(NPOI.SS.UserModel.CellType.String);
cell2.SetCellValue(product.Name);
NPOI.SS.UserModel.ICell cell3 = dataRow.GetCell(3);
cell3.SetCellType(NPOI.SS.UserModel.CellType.String);
var tempURL = "";
if (product.Photo.Trim().Length > 0)
{
tempURL = url + product.Photo;
}
cell3.SetCellValue(tempURL);
NPOI.SS.UserModel.ICell cell4 = dataRow.GetCell(4);
cell4.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell4.SetCellValue(product.Price);
NPOI.SS.UserModel.ICell cell5 = dataRow.GetCell(5);
cell5.SetCellType(NPOI.SS.UserModel.CellType.String);
cell5.SetCellValue(product.LastUpdate.ToString("yyyyMMdd HH:mm:ss"));
i += 1;
}
try
{
File.Delete(filepath);
}
catch { }
var memoryStream = new MemoryStream();
templateWorkbook.Write(memoryStream);
Byte[] content = memoryStream.ToArray();
System.IO.File.WriteAllBytes(filepath, content);
return(fileUrl);
}
public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, bool firstLineIsColumnHead)
{
System.Data.DataTable dataSource = new System.Data.DataTable();
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(dataSource);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
if (sheet == null)
{
return(dataSource);
}
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
if (firstRow != null)
{
for (int columnIndex = 0; columnIndex < firstRow.LastCellNum; columnIndex++)
{
if (firstLineIsColumnHead)
{
NPOI.SS.UserModel.ICell cell = firstRow.GetCell(columnIndex);
if (cell == null)
{
dataSource.Columns.Add();
}
else
{
dataSource.Columns.Add(cell.ToString());
}
}
else
{
dataSource.Columns.Add();
}
}
}
int startRowIndex = firstLineIsColumnHead ? 1 : 0;
for (int rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
{
System.Data.DataRow dataSourceRow = dataSource.NewRow();
dataSource.Rows.Add(dataSourceRow);
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);
if (row != null)
{
for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
if (cell != null)
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
dataSourceRow[columnIndex] = "";
break;
case NPOI.SS.UserModel.CellType.Boolean:
dataSourceRow[columnIndex] = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
dataSourceRow[columnIndex] = cell.ErrorCellValue;
break;
case NPOI.SS.UserModel.CellType.Formula:
dataSourceRow[columnIndex] = cell.NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
{
dataSourceRow[columnIndex] = cell.DateCellValue;
}
else
{
dataSourceRow[columnIndex] = cell.NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.String:
dataSourceRow[columnIndex] = cell.StringCellValue;
break;
default:
dataSourceRow[columnIndex] = cell.ToString();
break;
}
}
}
}
}
return(dataSource);
}
/// <summary>
/// 设置单元格值，支持合并单元格
/// </summary>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="firstCol"></param>
/// <param name="lastCol"></param>
/// <param name="value"></param>
/// <param name="sheet"></param>
public void SetCellValue(int firstRow, int lastRow, int firstCol, int lastCol, object value, NPOI.SS.UserModel.ISheet sheet)
{
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
for (int r = firstRow; r <= lastRow; r++)
{
row = sheet.GetRow(r);
if (row == null)
{
row = sheet.CreateRow(r);
}
for (int c = firstCol; c <= lastCol; c++)
{
cell = row.GetCell(c);
if (cell == null)
{
cell = row.CreateCell(c);
}
}
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
row = sheet.GetRow(firstRow);
cell = row.GetCell(firstCol);
if (value is bool)
{
cell.SetCellValue((bool)value);
}
if (value is string)
{
string svalue = (string)value;
if (svalue != null && svalue != "" && svalue.Substring(0, 1) == "=")
{
cell.SetCellFormula(svalue.Substring(1));
}
else
{
cell.SetCellValue((string)svalue);
}
}
if (value is NPOI.SS.UserModel.IRichTextString)
{
cell.SetCellValue((NPOI.SS.UserModel.IRichTextString)value);
}
if (value is DateTime)
{
if (this.DateTimeCellStyle != null)
{
cell.CellStyle = this.DateTimeCellStyle;
}
cell.SetCellValue((DateTime)value);
}
if (value is double)
{
cell.SetCellValue((double)value);
}
if (value is int)
{
cell.SetCellValue((int)value);
}
}
public FileResult OrderToExecl()
{
//取查询条件
var orderNo = Request.Get("OrderNo");
var checkOrderNo = string.IsNullOrWhiteSpace(orderNo);
var consignee = Request.Get("Consignee");
var checkConsignee = string.IsNullOrWhiteSpace(consignee);
var memberName = Request.Get("MemberName");
var checkMemberName = string.IsNullOrWhiteSpace(memberName);
var orderStatus = Request.Get("OrderStatus");
var checkOrderStatus = string.IsNullOrWhiteSpace(orderStatus);
var orderStatusInt = orderStatus.To <int>();
var refundStatus = Request.Get("RefundStatus");
var checkRefundStatus = string.IsNullOrWhiteSpace(refundStatus);
var refundStatusInt = refundStatus.To <int>();
var payStatus = Request.Get("PayStatus");
var checkPayStatus = string.IsNullOrWhiteSpace(payStatus);
var payStatusInt = payStatus.To <int>();
var shippingStatus = Request.Get("ShippingStatus");
var checkShippingStatus = string.IsNullOrWhiteSpace(shippingStatus);
var shippingStatusInt = shippingStatus.To <int>();
var paymentId = Request.Get("PaymentId");
var checkPaymentId = string.IsNullOrWhiteSpace(paymentId);
var createTimeBegin = Request.Get("CreateTimeBegin");
var checkCreateTimeBegin = string.IsNullOrWhiteSpace(createTimeBegin);
var createTimeBeginTime = createTimeBegin.To <DateTime>();
var createTimeEnd = Request.Get("CreateTimeEnd");
var checkCreateTimeEnd = string.IsNullOrWhiteSpace(createTimeEnd);
var createTimeEndTime = createTimeEnd.To <DateTime>();
Expression <Func <Order, bool> > expression =
l => (checkOrderNo || l.OrderNo.Contains(orderNo)) &&
(checkConsignee || l.Consignee.Contains(consignee)) &&
l.OrderStatus != OrderStatus.Deleted &&
(checkOrderStatus || (int)l.OrderStatus == orderStatusInt) &&
(checkRefundStatus || (int)l.RefundStatus == refundStatusInt) &&
(checkPayStatus || (int)l.PayStatus == payStatusInt) &&
(checkShippingStatus || (int)l.ShippingStatus == shippingStatusInt) &&
(checkCreateTimeBegin || l.CreateTime >= createTimeBeginTime) &&
(checkCreateTimeEnd || l.CreateTime <= createTimeEndTime) &&
(checkPaymentId || l.PaymentId.ToString() == paymentId) &&
(checkMemberName || l.MemberName.Contains(memberName));
var list = _orderService.GetList(expression).Select(o => new ViewModels.SimpleOderModel(o));
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
if (list.Any())
{
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("订单号");
row1.CreateCell(1).SetCellValue("商品名称");
row1.CreateCell(2).SetCellValue("规格");
row1.CreateCell(3).SetCellValue("数量");
row1.CreateCell(4).SetCellValue("下单时间");
row1.CreateCell(5).SetCellValue("收货人");
row1.CreateCell(6).SetCellValue("收货地址");
row1.CreateCell(7).SetCellValue("联系电话");
row1.CreateCell(8).SetCellValue("商品总价");
row1.CreateCell(9).SetCellValue("物流费用");
row1.CreateCell(10).SetCellValue("积分折抵");
row1.CreateCell(11).SetCellValue("应付金额");
row1.CreateCell(12).SetCellValue("订单状态");
var i = 0;
var cs = book.CreateCellStyle();
cs.WrapText = true;
foreach (var item in list)
{
string n = "";
string m = "";
string k = "";
for (int j = 0; j < item.OrderGoods.Count; j++)
{
if (j == item.OrderGoods.Count - 1)
{
n += item.OrderGoods[j].GoodsName;
m += item.OrderGoods[j].GoodsAttribute;
k += item.OrderGoods[j].Quantity + item.OrderGoods[j].Unit;
}
else
{
n += item.OrderGoods[j].GoodsName + "\n";
m += item.OrderGoods[j].GoodsAttribute + "\n";
k += item.OrderGoods[j].Quantity + item.OrderGoods[j].Unit + "\n";
}
}
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(item.OrderNo);
rowtemp.CreateCell(1).SetCellValue(n);
rowtemp.CreateCell(2).SetCellValue(m);
rowtemp.CreateCell(3).SetCellValue(k);
if (item.OrderGoods.Count > 1)
{
rowtemp.GetCell(1).CellStyle = cs;
rowtemp.GetCell(2).CellStyle = cs;
rowtemp.GetCell(3).CellStyle = cs;
}
rowtemp.CreateCell(4).SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", item.CreateTime));
rowtemp.CreateCell(5).SetCellValue(item.Consignee);
rowtemp.CreateCell(6).SetCellValue(item.RegionName + item.Address);
rowtemp.CreateCell(7).SetCellValue(item.Tel);
rowtemp.CreateCell(8).SetCellValue(item.GoodsAmount.ToString("#0.00"));
rowtemp.CreateCell(9).SetCellValue(item.ShippingFee.ToString("#0.00"));
rowtemp.CreateCell(10).SetCellValue(item.IntegralMoney.ToString("#0.00"));
rowtemp.CreateCell(11).SetCellValue(item.PayFee.ToString("#0.00"));
var statusName = item.OrderStatus.Description();
if (item.RefundStatus > 0)
{
statusName += "(" + item.RefundStatus.Description() + ")";
}
if (item.EvaluateStatus > 0)
{
statusName += "(" + item.EvaluateStatus.Description() + ")";
}
rowtemp.CreateCell(12).SetCellValue(statusName);
i++;
}
}
// 写入到客户端
var ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
var dt = DateTime.Now;
var dateTime = dt.ToString("yyMMddHHmmssfff");
var fileName = "订单列表" + dateTime + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
{
DataSet ds = null;
HSSFWorkbook workbook;
try
{
workbook = new HSSFWorkbook(excelFileStream);
ds = new DataSet();
for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
{
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(a);
DataTable table = new DataTable();
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i + 1;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
{
// 如果遇到第一个空行，则不再继续向后读取
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
ds.Tables.Add(table);
}
excelFileStream.Close();
return(ds);
}
catch (Exception e)
{
return(ds);
}
finally
{
if (ds != null)
{
ds.Dispose();
}
workbook = null;
}
}
