public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int startRowIndex)
{
System.Data.DataTable dataSource = new System.Data.DataTable();
dataSource.Columns.Add("Index", typeof(string));
dataSource.Columns.Add("IndexNPOI", typeof(string));
dataSource.Columns.Add("Name", typeof(string));
dataSource.Columns.Add("Description", typeof(string));
dataSource.Columns.Add("HexString", typeof(string));
// 获取Workbook。
NPOI.SS.UserModel.IWorkbook workbook = NPOIExtension.GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(dataSource);
}
// 获取Sheet
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
for (int i = startRowIndex; i < sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
System.Data.DataRow dataSourceRow = dataSource.NewRow();
dataSource.Rows.Add(dataSourceRow);
for (int j = 0; j < dataSource.Columns.Count; j++)
{
dataSourceRow[j] = row.GetCell(j).ToString();
}
}
return(dataSource);
}
/// <summary>
/// Implementació default
/// </summary>
/// <param name="workbook"></param>
/// <param name="resumenProceso"></param>
/// <returns></returns>
public override bool ValidarPlantillaExcelImportacion(NPOI.SS.UserModel.IWorkbook workBookImportar, Common.Dtos.ResumenProcesoImportacionExcelDto resumenProceso)
{
bool puedeContinuar = true;
var espacioNombresPlantilla = "";
if (workBookImportar.NumberOfSheets > 0)
{
//2019 Validar opcionalmente que la plantilla corresponda al espacio de nombres de la taxonomía
//en la hoja 1, renglón 1, columna 2 se colocará el espacio de nombres de la taxonomía
var hoja1 = workBookImportar.GetSheetAt(0);
var renglon1 = hoja1.GetRow(0);
if (renglon1 != null)
{
var celdaValidacion = renglon1.GetCell(1);
if (celdaValidacion != null)
{
espacioNombresPlantilla = celdaValidacion.StringCellValue;
}
}
}
if (!this.documentoInstancia.EspacioNombresPrincipal.Equals(espacioNombresPlantilla))
{
resumenProceso.InformeErrores.Add(new InformeErrorImportacion()
{
Mensaje = "El documento Excel no pertenece a la taxonomía que está utilizando (" + this.documentoInstancia.Taxonomia.nombreAbax + ")." +
"\nAsegúrese que el documento corresponda a la taxonomía de la que desea importar los datos."
});
puedeContinuar = false;
}
return(puedeContinuar);
}
/// <summary>
/// Implementació default
/// </summary>
/// <param name="workbook"></param>
/// <param name="resumenProceso"></param>
/// <returns></returns>
public override bool ValidarPlantillaExcelImportacion(NPOI.SS.UserModel.IWorkbook workBookImportar, Common.Dtos.ResumenProcesoImportacionExcelDto resumenProceso)
{
bool puedeContinuar = true;
var espacioNombresPlantilla = "";
var valorDefaultValidacionPlantilla2014 = "";
if (workBookImportar.NumberOfSheets > 0)
{
//2014 Validar opcionalmente que la plantilla no corresponda al espacio de nombres de la taxonomía
//en la hoja 1, renglón 1, columna 2 debe encontrarse un espacio.
var hoja1 = workBookImportar.GetSheetAt(0);
var renglon1 = hoja1.GetRow(0);
if (renglon1 != null)
{
var celdaValidacion = renglon1.GetCell(1);
if (celdaValidacion != null)
{
espacioNombresPlantilla = celdaValidacion.StringCellValue;
}
}
}
if (!valorDefaultValidacionPlantilla2014.Equals(espacioNombresPlantilla))
{
resumenProceso.InformeErrores.Add(new InformeErrorImportacion()
{
Mensaje = "La plantilla de importación para la taxonomía (" + this.documentoInstancia.Taxonomia.nombreAbax + ") " +
"\n no es la esperada."
});
puedeContinuar = false;
}
return(puedeContinuar);
}
public static NPOI.SS.UserModel.ICellStyle GetCellStyle(this NPOI.SS.UserModel.IWorkbook workbook, short backColorIndex, short fontColorIndex, System.Drawing.Font font, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General, NPOI.SS.UserModel.VerticalAlignment verticalAlignment = NPOI.SS.UserModel.VerticalAlignment.None, NPOI.SS.UserModel.BorderStyle borderLeft = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderTop = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderRight = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderBottom = NPOI.SS.UserModel.BorderStyle.None)
{
NPOI.SS.UserModel.ICellStyle cellStyle = workbook.CreateCellStyle();
if (backColorIndex >= 8 && backColorIndex <= 63)
{
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cellStyle.FillForegroundColor = backColorIndex;
}
cellStyle.Alignment = horizontalAlignment;
cellStyle.VerticalAlignment = verticalAlignment;
cellStyle.BorderLeft = borderLeft;
cellStyle.BorderTop = borderTop;
cellStyle.BorderRight = borderRight;
cellStyle.BorderBottom = borderBottom;
if (font != null)
{
NPOI.SS.UserModel.IFont cellFont = workbook.CreateFont();
if (fontColorIndex >= 8 && fontColorIndex <= 63)
{
cellFont.Color = fontColorIndex;
}
cellFont.FontName = font.Name;
cellFont.FontHeight = font.Size;
cellFont.Boldweight = (short)(font.Bold ? NPOI.SS.UserModel.FontBoldWeight.Bold : NPOI.SS.UserModel.FontBoldWeight.Normal);
cellStyle.SetFont(cellFont);
}
return(cellStyle);
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
public static void SaveToFile(NPOI.SS.UserModel.IWorkbook workbook, string fileName)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.ReadWrite);
workbook.Write(fileStream);
fileStream.Close();
workbook.Close();
}
public static Workbook GetAdapter(this NpoiWorkbook workbook)
{
if (null == workbook)
{
return(null);
}
return(new Workbook(workbook));
}
public static System.Data.DataTable GetDataTable(string fileName, string sheetName, bool firstRowIsColumnHead, int maxColumnCount, int startColumnIndex, int startRowIndex)
{
NPOI.SS.UserModel.IWorkbook iWorkbook = Extension.NPOIMethod.GetWorkbookFromExcelFile(fileName);
if (iWorkbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet iSheet = iWorkbook.GetSheet(sheetName);
return(GetDataTable(iSheet, firstRowIsColumnHead, maxColumnCount, startColumnIndex, startRowIndex));
}
protected void btn_export_Click(object sender, EventArgs e)
{
dt = (DataTable)ViewState["dt"];
dt.Columns.Remove("roletype");
dt.Columns.Remove("subType");
Excelexport_NPOI.NPOI_ExportExcel Export = new Excelexport_NPOI.NPOI_ExportExcel();
NPOI.SS.UserModel.IWorkbook workbook = Export.ExportToFile(dt, "群組,單位,工號,姓名,分機,院區別,在職狀態".Split(','), null, "管理者群組維護", "2003", false, string.Empty, 1, 0, null);
Export.ExportExeclfile();
}
public static NPOI.SS.UserModel.IWorkbook GetWorkbookFromExcelFile(string fileName)
{
NPOI.SS.UserModel.IWorkbook workbook = null;
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
if (!fileInfo.Exists)
{
return(workbook);
}
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fileStream);
fileStream.Close();
return(workbook);
}
/// <summary>
/// 获取Excel信息
/// </summary>
/// <param name="filePath"></param>
/// <author>范永坚 2017-08-09</author>
public List <string> LoadFile(string filePath)
{
var prevCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
_filePath = filePath;
SheetNames = new List <string>();
using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
_workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
}
return(GetSheetNames());
}
public static System.Data.DataTable GetDataTableFromExcelFile(string fileName, string sheetName, int startRowIndex, int startColumnIndex, bool firstRowIsColumnHead = true, bool autoAddColumn = false, bool ignoreBlankRow = true)
{
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
return(null);
}
return(GetDataTableFromSheet(sheet, startRowIndex, startColumnIndex, firstRowIsColumnHead, autoAddColumn, ignoreBlankRow));
}
public static NPOI.SS.UserModel.IWorkbook GetWorkbookFromExcelFile(string fileName)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
if (!fileInfo.Exists)
{
return(null);
}
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
NPOI.SS.UserModel.IWorkbook iWorkbook = null;
iWorkbook = NPOI.SS.UserModel.WorkbookFactory.Create(fileStream);
//if (fileInfo.Extension == ".xls")
//{ iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream); }
//else if (fileInfo.Extension == ".xlsx")
//{ iWorkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fileStream); }
fileStream.Close();
return(iWorkbook);
}
/// <summary>
/// 获取指定格式的工作簿
/// </summary>
/// <param name="excelFormat">格式类型</param>
/// <returns></returns>
public static NPOI.SS.UserModel.IWorkbook GetWorkbook(ExcelFormat excelFormat)
{
NPOI.SS.UserModel.IWorkbook workbook = null;
switch (excelFormat)
{
case ExcelFormat.Xls:
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
break;
case ExcelFormat.Xlsx:
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
break;
default:
break;
}
return(workbook);
}
public static System.Data.DataSet GetDataSetFromExcelFile(string fileName, bool firstRowIsColumnHead = true, bool ignoreBlankRow = true, bool ignoreHiddenSheet = true)
{
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(null);
}
System.Data.DataSet dataSet = new System.Data.DataSet();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
bool isHiddenSheet = workbook.IsSheetHidden(i) || workbook.IsSheetVeryHidden(i);
if (ignoreHiddenSheet && isHiddenSheet)
{
continue;
}
dataSet.Tables.Add(GetDataTableFromSheet(workbook.GetSheetAt(i), 0, 0, true, true, true));
}
return(dataSet);
}
public static System.Data.DataSet GetDataSet(string fileName, bool firstRowIsColumnHead, bool ignoreHiddenSheet)
{
NPOI.SS.UserModel.IWorkbook iWorkbook = Extension.NPOIMethod.GetWorkbookFromExcelFile(fileName);
if (iWorkbook == null)
{
return(null);
}
System.Data.DataSet dataSet = new System.Data.DataSet();
for (int iSheetIndex = 0; iSheetIndex < iWorkbook.NumberOfSheets; iSheetIndex++)
{
bool isHiddenSheet = iWorkbook.IsSheetHidden(iSheetIndex) || iWorkbook.IsSheetHidden(iSheetIndex);
if (ignoreHiddenSheet && isHiddenSheet)
{
continue;
}
dataSet.Tables.Add(GetDataTable(iWorkbook.GetSheetAt(iSheetIndex), firstRowIsColumnHead, 0, 0, 0));
}
return(dataSet);
}
public static NPOI.SS.UserModel.IWorkbook GetWorkbookFromExcelFile(string fileName)
{
NPOI.SS.UserModel.IWorkbook workbook = null;
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
if (!fileInfo.Exists)
{
return(workbook);
}
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
if (fileInfo.Extension == ".xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fileStream);
}
else if (fileInfo.Extension == ".xls")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fileStream);
}
fileStream.Close();
return(workbook);
}
public static void SaveToFile(string fileName, System.Data.DataSet dataSet, bool firstRowIsColumnHead)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
NPOI.SS.UserModel.IWorkbook iWorkbook = null;
if (fileInfo.Extension == ".xls")
{
iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else if (fileInfo.Extension == ".xlsx")
{
iWorkbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
for (int dataTableIndex = 0; dataTableIndex < dataSet.Tables.Count; dataTableIndex++)
{
System.Data.DataTable dataTable = dataSet.Tables[dataTableIndex];
NPOI.SS.UserModel.ISheet iSheet = iWorkbook.CreateSheet(dataTable.TableName);
FullSheet(iSheet, dataTable, firstRowIsColumnHead);
}
SaveToFile(fileName, iWorkbook);
}
public static System.Data.DataTable GetDataTableFromExcelFile(string fileName, string sheetName, int startRowIndex, int startColumnIndex, bool ignoreBlankRow, params string[] columnHeadNames)
{
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(sheetName);
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 static bool SaveExcel(NPOI.SS.UserModel.IWorkbook WorkBook, string FileName, bool OpenAfterSave, bool ShowErrorMessage = true)
{
try
{
using (var fs = new System.IO.FileStream(FileName, FileMode.Create, System.IO.FileAccess.Write))
{ WorkBook.Write(fs); }
}
catch (Exception ex)
{
if (ShowErrorMessage)
{
MessageBox.Show("При попытке сохранения файла печатной формы возникла ошибка:\n" + ex.ToString(), "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
if (OpenAfterSave)
{
System.Diagnostics.Process.Start(FileName);
}
return(true);
}
public static void CreateExcelColorFile(string fileName)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
// 创建Workbook。
NPOI.SS.UserModel.IWorkbook workbook = null;// new NPOI.XSSF.UserModel.XSSFWorkbook();
if (fileInfo.Extension == ".xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else if (fileInfo.Extension == ".xls")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
return;
}
// 创建Sheet
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("ExcelColor");
// 设置列宽。
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 15 * 256);
// 创建标题。
int rowIndex = 0;
NPOI.SS.UserModel.ICell cell = sheet.CreateRow(rowIndex).CreateCell(0);
cell.SetCellValue("Excel颜色");
cell.CellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Arial", 16, System.Drawing.FontStyle.Bold));
// 空一行
rowIndex++;
// 创建标题行
rowIndex++;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle columnHeadCellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
cell = row.CreateCell(0);
cell.SetCellValue("索引");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("索引（NPOI）");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("名称");
cell.CellStyle = columnHeadCellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 2, 3));
cell = row.CreateCell(4);
cell.SetCellValue("十六进制代码");
cell.CellStyle = columnHeadCellStyle;
ExcelColor.KnownColors.ForEach(x =>
{
rowIndex++;
NPOI.SS.UserModel.IRow contentRow = sheet.CreateRow(rowIndex);
contentRow.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle contentCellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
NPOI.SS.UserModel.ICell contentCell = contentRow.CreateCell(0);
contentCell.SetCellValue(x.Index);
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(1);
contentCell.SetCellValue(x.IndexNPOI + "（NPOI）");
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(2);
contentCell.SetCellValue(x.Name);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Right, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(3);
contentCell.SetCellValue(x.Description);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Left, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(4);
contentCell.SetCellValue(x.HexString);
contentCell.CellStyle = contentCellStyle;
});
// 保存到文件。
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite);
workbook.Write(fileStream);
fileStream.Close();
workbook.Close();
}
public void DownlodExcel()
{
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
sflg.FileName = "采购单细节导入模板表.xls";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("采购细节表");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("品名");
#region 医疗器械(这里不用)
//if ((int)this.toolStripComboBox1.ComboBox.SelectedValue == (int)PurchaseDrugTypes.医疗器械)
//{
// cell = row.CreateCell(1);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("型号");
// cell = row.CreateCell(2);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("规格");
// cell = row.CreateCell(3);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("单位");
// cell = row.CreateCell(4);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("生产厂家");
// cell = row.CreateCell(5);
// cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
// cell.SetCellValue("数量");
// cell = row.CreateCell(6);
// cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
// cell.SetCellValue("单价");
// cell = row.CreateCell(7);
// cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
// cell.SetCellValue("税率(%)");
//}
//else
#endregion
{
cell = row.CreateCell(1);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("剂型");
cell = row.CreateCell(2);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("规格");
cell = row.CreateCell(3);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("单位");
cell = row.CreateCell(4);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("生产厂家");
cell = row.CreateCell(5);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("产地");
cell = row.CreateCell(6);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue("数量");
cell = row.CreateCell(7);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue("单价");
cell = row.CreateCell(8);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue("税率(%)");
}
// 写入
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
public void Save(string FileName, Model model)
{
try
{
int idx = FileName.LastIndexOf('.');
string ext = FileName.Substring(idx + 1);
NPOI.SS.UserModel.IWorkbook workbook = null;
if (File.Exists(FileName))
{
using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read))
{
workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
int nSheets = workbook.NumberOfSheets;
if (nSheets > 0)
{
var sheet = workbook.GetSheetAt(0);
int nRows = sheet.LastRowNum + 1;
var row = sheet.CreateRow(nRows);
row.CreateCell(0).SetCellValue(model.Id);
row.CreateCell(1).SetCellValue(model.Time);
row.CreateCell(2).SetCellValue(model.QRCode);
row.CreateCell(3).SetCellValue(model.Flow);
row.CreateCell(4).SetCellValue(model.APressure);
row.CreateCell(5).SetCellValue(model.BPressure);
}
fs.Close();
}
}
else
{
if (ext == "xls")
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else if (ext == "xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
return;
}
var sheet = workbook.CreateSheet();
var row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("序号");
row0.CreateCell(1).SetCellValue("时间");
row0.CreateCell(2).SetCellValue("条码");
row0.CreateCell(3).SetCellValue("流量/ml");
row0.CreateCell(4).SetCellValue("A胶压力/psi");
row0.CreateCell(5).SetCellValue("B胶压力/psi");
var row = sheet.CreateRow(1);
row.CreateCell(0).SetCellValue(model.Id);
row.CreateCell(1).SetCellValue(model.Time);
row.CreateCell(2).SetCellValue(model.QRCode);
row.CreateCell(3).SetCellValue(model.Flow);
row.CreateCell(4).SetCellValue(model.APressure);
row.CreateCell(5).SetCellValue(model.BPressure);
}
using (FileStream fs_write = new FileStream(FileName, FileMode.Create))
{
workbook.Write(fs_write);
fs_write.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button6_Click(object sender, EventArgs e)
{
List <FishEntity.CallRecordsEntity> list = new List <FishEntity.CallRecordsEntity>();
for (int i = 0; i < 10; i++)
{
FishEntity.CallRecordsEntity entity = new FishEntity.CallRecordsEntity();
entity.code = "00000" + i;
entity.customer = "杭州" + i;
entity.linkman = "金" + i;
entity.mobile = "13757193476";
list.Add(entity);
}
string startDate = "2015-03-01";
string endDate = "2015-03-25";
string templatepath = Application.StartupPath + "\\template\\callrecord.xls";
FileStream fs = new FileStream(templatepath, FileMode.Open, FileAccess.Read);
NPOI.SS.UserModel.IWorkbook workbook = null;
workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
int firstRow = sheet.FirstRowNum;
int lastRow = sheet.LastRowNum;
System.Collections.Hashtable hs = new System.Collections.Hashtable();
hs.Add("startDate", startDate);
hs.Add("endDate", endDate);
hs.Add("items", list);
int arrStart = -1;
int arrItem = -1;
int arrEnd = -1;
List <FishEntity.CallRecordsEntity> items = null;
for (int idx = firstRow; idx <= lastRow; idx++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(idx);
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
string val = cell.ToString();
if (string.IsNullOrEmpty(val))
{
continue;
}
foreach (System.Collections.DictionaryEntry entry in hs)
{
string key1 = "<jx:forEach items=\"${" + entry.Key.ToString() + "}\" var=\"item\"}>";
if (val.Equals(key1))
{
arrStart = idx;
items = entry.Value as List <FishEntity.CallRecordsEntity>;
break;
}
string key2 = "</jx:forEach>";
if (val.Equals(key2))
{
arrEnd = idx;
break;
}
string key = "${" + entry.Key.ToString() + "}";
if (val.Contains(key))
{
val = val.Replace(key, entry.Value.ToString());
}
}
}
}
if (arrStart >= 0 && arrEnd >= 2)
{
arrItem = arrStart + 1;
NPOI.SS.UserModel.IRow rrrow = sheet.GetRow(arrItem);
List <KV> columns = GetItemNames(rrrow);
NPOI.SS.UserModel.IRow row = null;
for (int idx = 0; idx < items.Count; idx++)
{
int tttidx = arrStart + idx;
if (tttidx != arrItem)
{
row = sheet.CreateRow(tttidx);
NPOI.SS.UserModel.IRow ffrow = sheet.GetRow(arrItem);
//row = sheet.CopyRow(arrItem, tttidx);
for (int i = 0; i < columns.Count; i++)
{
row.CreateCell(i);
}
}
else
{
row = rrrow;
}
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
foreach (KV kv in columns)
{
if (kv.colIdx == cell.ColumnIndex)
{
System.Reflection.PropertyInfo prop = items[idx].GetType().GetProperty(kv.name);
if (prop != null)
{
string temp = prop.GetValue(items[idx], null).ToString();
cell.SetCellValue(temp);
}
}
}
}
}
if ((arrEnd - arrStart + 1) > items.Count)
{
//TODO
}
string fpath = Application.StartupPath + "\\test.xls";
System.IO.FileStream fss = new FileStream(fpath, FileMode.Create, FileAccess.Write);
workbook.Write(fss);
fss.Close();
}
}
public void Export(Stream ms, DataTable dt, string postfix)
{
//var fileName = m_Handler.GetFileName();
NPOI.SS.UserModel.IWorkbook book = null;
if (postfix == ".xls")
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else if (postfix == ".xlsx")
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名");
}
m_Handler.Encode(ref dt);
//WriteExcel(ref book, dt);
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
//// 写入
//MemoryStream ms = new MemoryStream();
book.Write(ms);
book = null;
//using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
//ms.Close();
//ms.Dispose();
}
private void sb_save_Click(object sender, EventArgs e)
{
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
int aa = 0;
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
foreach (XtraTabPage trp in this.xtraTabControl1.TabPages)
{
XtraTabPage xinka = trp;
try
{
this.active_nepCalaTable = xinka.Controls[0] as NepCalaTable;
}
catch (Exception err)
{
MessageBox.Show("出现了错误： " + err.Message);
}
aa += 1;
//this.gridView1.ExportToXls(sflg.FileName);
//NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(string.Format("test_{0}", aa));
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(xinka.Text);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
GridView ac = this.active_nepCalaTable.GC;
int count = ac.Columns.Count;
for (int i = 0; i < count; i++)
{
string Caption = ac.Columns[i].Caption;
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(Caption);
index++;
}
// 添加数据
DataTable dt = this.active_nepCalaTable.DDT;
for (int j = 0; j < dt.Rows.Count; j++)
{
index = 0;
row = sheet.CreateRow(j + 1);
for (int k = 0; k < count; k++)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
//cell.SetCellValue(this.gridView1.GetRowCellValue(i, item).ToString());
cell.SetCellValue(dt.Rows[j][k].ToString());
index++;
}
}
NPOI.SS.UserModel.IRow r1 = sheet.GetRow(0);
// 第一排
NPOI.SS.UserModel.ICell cell1 = r1.CreateCell(12);
cell1.SetCellType(NPOI.SS.UserModel.CellType.String);
cell1.SetCellValue("激发点距");
NPOI.SS.UserModel.ICell cell11 = r1.CreateCell(13);
cell11.SetCellType(NPOI.SS.UserModel.CellType.String);
cell11.SetCellValue(this.active_nepCalaTable.jifa_dianju.Text);
// 第二排
r1 = sheet.GetRow(1);
NPOI.SS.UserModel.ICell cell2 = r1.CreateCell(12);
cell2.SetCellType(NPOI.SS.UserModel.CellType.String);
cell2.SetCellValue("接收点距");
NPOI.SS.UserModel.ICell cell21 = r1.CreateCell(13);
cell21.SetCellType(NPOI.SS.UserModel.CellType.String);
cell21.SetCellValue(this.active_nepCalaTable.jieshou_dianju.Text);
// 第三排
r1 = sheet.GetRow(2);
NPOI.SS.UserModel.ICell cell3 = r1.CreateCell(12);
cell3.SetCellType(NPOI.SS.UserModel.CellType.String);
cell3.SetCellValue("接收点数");
NPOI.SS.UserModel.ICell cell31 = r1.CreateCell(13);
cell31.SetCellType(NPOI.SS.UserModel.CellType.String);
cell31.SetCellValue(this.active_nepCalaTable.jieshou_dianshu.Text);
// 第四排
r1 = sheet.GetRow(3);
NPOI.SS.UserModel.ICell cell4 = r1.CreateCell(12);
cell4.SetCellType(NPOI.SS.UserModel.CellType.String);
cell4.SetCellValue("激发线距");
NPOI.SS.UserModel.ICell cell41 = r1.CreateCell(13);
cell41.SetCellType(NPOI.SS.UserModel.CellType.String);
cell41.SetCellValue(this.active_nepCalaTable.jifa_xianju.Text);
// 第五排
r1 = sheet.GetRow(4);
NPOI.SS.UserModel.ICell cell5 = r1.CreateCell(12);
cell5.SetCellType(NPOI.SS.UserModel.CellType.String);
cell5.SetCellValue("接受线距");
NPOI.SS.UserModel.ICell cell51 = r1.CreateCell(13);
cell51.SetCellType(NPOI.SS.UserModel.CellType.String);
cell51.SetCellValue(this.active_nepCalaTable.jieshou_xianju.Text);
// 第六排
r1 = sheet.GetRow(5);
NPOI.SS.UserModel.ICell cell6 = r1.CreateCell(12);
cell6.SetCellType(NPOI.SS.UserModel.CellType.String);
cell6.SetCellValue("纵向滚动距离");
NPOI.SS.UserModel.ICell cell61 = r1.CreateCell(13);
cell61.SetCellType(NPOI.SS.UserModel.CellType.String);
cell61.SetCellValue(this.active_nepCalaTable.muban_zong.Text);
// 第7排
r1 = sheet.GetRow(6);
NPOI.SS.UserModel.ICell cell7 = r1.CreateCell(12);
cell7.SetCellType(NPOI.SS.UserModel.CellType.String);
cell7.SetCellValue("模板炮");
NPOI.SS.UserModel.ICell cell71 = r1.CreateCell(13);
cell71.SetCellType(NPOI.SS.UserModel.CellType.String);
cell71.SetCellValue(this.active_nepCalaTable.mobanpao.Text);
// 第8排
r1 = sheet.GetRow(7);
NPOI.SS.UserModel.ICell cell8 = r1.CreateCell(12);
cell8.SetCellType(NPOI.SS.UserModel.CellType.String);
cell8.SetCellValue("接收线数");
NPOI.SS.UserModel.ICell cell81 = r1.CreateCell(13);
cell81.SetCellType(NPOI.SS.UserModel.CellType.String);
cell81.SetCellValue(this.active_nepCalaTable.jieshou_xianshu.Text);
// 第9排
r1 = sheet.GetRow(8);
NPOI.SS.UserModel.ICell cell9 = r1.CreateCell(12);
cell9.SetCellType(NPOI.SS.UserModel.CellType.String);
cell9.SetCellValue("横向滚动距离");
NPOI.SS.UserModel.ICell cell91 = r1.CreateCell(13);
cell91.SetCellType(NPOI.SS.UserModel.CellType.String);
cell91.SetCellValue(this.active_nepCalaTable.muban_heng.Text);
// 第10排
r1 = sheet.GetRow(9);
NPOI.SS.UserModel.ICell cell10 = r1.CreateCell(12);
cell10.SetCellType(NPOI.SS.UserModel.CellType.String);
cell10.SetCellValue("布设接受线数");
NPOI.SS.UserModel.ICell cell101 = r1.CreateCell(13);
cell101.SetCellType(NPOI.SS.UserModel.CellType.String);
cell101.SetCellValue(this.active_nepCalaTable.bushe_jieshouxianshu.Text);
// 第11排
r1 = sheet.GetRow(10);
NPOI.SS.UserModel.ICell cell1_1 = r1.CreateCell(12);
cell1_1.SetCellType(NPOI.SS.UserModel.CellType.String);
cell1_1.SetCellValue("布设激发线数");
NPOI.SS.UserModel.ICell cell111 = r1.CreateCell(13);
cell111.SetCellType(NPOI.SS.UserModel.CellType.String);
cell111.SetCellValue(this.active_nepCalaTable.bushe_jifaxianshu.Text);
// 写入
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//book = null;
using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
book = null;
MessageBox.Show("保存成功了");
}
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);
}
private void explort()
{
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
//this.gridView1.ExportToXls(sflg.FileName);
//NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_001");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataGridViewColumn item in this.dataGridView2.Columns)
{
if (item.Visible)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.HeaderText);
index++;
}
}
// 添加数据
for (int i = 0; i < this.dataGridView2.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataGridViewColumn item in this.dataGridView2.Columns)
{
if (item.Visible)
{
if (dataGridView2.Rows[i].Cells[item.HeaderText].Value != null)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dataGridView2.Rows[i].Cells[item.HeaderText].Value.ToString().Trim());
}
index++;
}
}
}
// 写入
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
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 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);
}
