/// <summary>
/// 获取多个isheet
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public List <ISheet> GetSheet(string filePath)
{
List <ISheet> sheet1 = new List <ISheet>();
using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.IndexOf(".xlsx") > 0) // 2007版本
{
NPOI.XSSF.UserModel.XSSFWorkbook workBook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
for (int i = 0; i < workBook.NumberOfSheets; i++)
{
sheet1.Add(workBook.GetSheetAt(i));
}
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
for (int i = 0; i < workBook.NumberOfSheets; i++)
{
sheet1.Add(workBook.GetSheetAt(i));
}
}
}
return(sheet1);
}
/// <summary>
/// 向excel添加图片
/// </summary>
/// <param name="XlsFilePath"></param>
/// <param name="pictureData"></param>
/// <param name="sheetName"></param>
public static void AddPicture(string XlsFilePath, byte[] pictureData, bool isNewSheet, string sheetName = "PicTure")
{
FileStream fs = File.OpenRead(XlsFilePath);
//IWorkbook workbook = new XSSFWorkbook(fs);
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); // (NPOI.XSSF.UserModel.XSSFWorkbook)WorkbookFactory.Create(XlsFilePath);
fs.Close();
int pictureIdx = book.AddPicture(pictureData, PictureType.JPEG);
NPOI.SS.UserModel.ISheet sheet = null;
if (!isNewSheet)
{
sheet = book.GetSheetAt(0);
}
else
{
sheet = book.CreateSheet(sheetName);
}
IDrawing patriarch = sheet.CreateDrawingPatriarch();
NPOI.XSSF.UserModel.XSSFClientAnchor anchor = new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
using (FileStream fileStream = File.Open(XlsFilePath,
FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
book.Write(fileStream);
fileStream.Close();
}
}
public void Error_GetCellValue_Test()
{
//Arrange
IReadExcelService svc = new ReadExcelService();
var fileName = "W610x155.xlsx";
var fullPath = $"../../../Mock/ModeloBd/{fileName}";
NPOI.SS.UserModel.ISheet passesDesbastadoresSheet = null;
System.IO.FileStream stream = File.OpenRead(fullPath);
var xssfWorkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
passesDesbastadoresSheet = xssfWorkbook.GetSheetAt(0);
//Act
var cr = new NPOI.SS.Util.CellReference("L20");
var row = passesDesbastadoresSheet.GetRow(cr.Row);
var cell = row.GetCell(cr.Col);
Assert.True(cell.NumericCellValue == 80);
cr = new NPOI.SS.Util.CellReference("O20");
row = passesDesbastadoresSheet.GetRow(cr.Row);
cell = row.GetCell(cr.Col);
Assert.True(cell.NumericCellValue > 397);
cr = new NPOI.SS.Util.CellReference("P20");
row = passesDesbastadoresSheet.GetRow(cr.Row);
cell = row.GetCell(cr.Col);
Assert.True(cell.NumericCellValue > 117858);
cr = new NPOI.SS.Util.CellReference("R20");
row = passesDesbastadoresSheet.GetRow(cr.Row);
cell = row.GetCell(cr.Col);
Assert.True(cell.NumericCellValue == 0);
cr = new NPOI.SS.Util.CellReference("S20");
row = passesDesbastadoresSheet.GetRow(cr.Row);
cell = row.GetCell(cr.Col);
Assert.True(cell.NumericCellValue == 1);
cr = new NPOI.SS.Util.CellReference("T20");
row = passesDesbastadoresSheet.GetRow(cr.Row);
cell = row.GetCell(cr.Col);
Assert.True(cell.NumericCellValue > 9.7);
}
/// <summary>
/// 导入excel文件
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private static string ImportExcel(string filePath)
{
string result = "";
NPOI.SS.UserModel.ISheet sheet = null;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
sheet = hssfworkbook.GetSheetAt(0);
}
}
catch (Exception)
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
NPOI.XSSF.UserModel.XSSFWorkbook hssfworkbook2 = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
sheet = hssfworkbook2.GetSheetAt(0);
}
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
if (row.LastCellNum != -1)
{
if ((dr[0] == null | dr[0].ToString() == "") && (dr[1] == null | dr[1].ToString() == "") && (dr[2] == null | dr[2].ToString() == ""))
{
}
else
{
dt.Rows.Add(dr);
}
}
}
dt = Distinct(dt, new string[] { "A", "B", "C" });
DataView myDataView = new DataView(dt);
string[] strComuns = { "C" };
if (myDataView.ToTable(true, strComuns).Rows.Count < dt.Rows.Count)
{
result = "用户表存在相同的账号数据";
return(result);
}
DbHelper dp = new DbHelper();
foreach (DataRow dr in dt.Rows)
{
string strsql = "insert into hs_user1 values ('" + dr["B"] + "','" + dr["C"] + "')";
dp.RunTxt(strsql);
}
result = "导入成功";
return(result);
}
/// <summary>
/// 生成excel
/// </summary>
/// <param name="columns">栏位，格式为：{
/// column_name:'栏位名称',
/// column_display_name:'表头显示名称'
/// }
/// </param>
/// <param name="data">数据，格式为：
/// {
///column_name1:'值',
///column_name2:'值',
/// .....
///column_nameN:'值',
///}
/// </param>
/// <returns></returns>
public byte[] GenerateExcel(IEnumerable <FrameDLRObject> columns, IEnumerable <FrameDLRObject> data)
{
IWorkbook workbook = null;
try
{
using (var ms = new MemoryStream())
{
ms.Seek(0, SeekOrigin.Begin);
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(ms);
}
ISheet sheet = workbook.GetSheetAt(0);
var index = 0;
//设置表头
var header = sheet.CreateRow(index);
var headerstyle = workbook.CreateCellStyle();
headerstyle.Alignment = HorizontalAlignment.Center;
headerstyle.BorderBottom = BorderStyle.Thin;
headerstyle.BorderTop = BorderStyle.Thin;
headerstyle.BorderLeft = BorderStyle.Thin;
headerstyle.BorderRight = BorderStyle.Thin;
headerstyle.FillBackgroundColor = IndexedColors.BlueGrey.Index;
headerstyle.FillPattern = FillPattern.SolidForeground;
var cellindex = 0;
foreach (dynamic item in columns)
{
var cell = header.CreateCell(cellindex);
cell.CellStyle = headerstyle;
cell.SetCellValue(item.column_display_name);
cellindex++;
}
cellindex = 0;
var datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Center;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
foreach (var item in data)
{
index++;
var row = sheet.CreateRow(index);
foreach (dynamic c in columns)
{
var cell = row.CreateCell(cellindex);
cell.CellStyle = datastyle;
var v = item.GetValue(c.column_name);
if (v == null)
{
cell.SetCellValue("");
}
else if (v is DateTime)
{
cell.SetCellValue(((DateTime)v).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
cell.SetCellValue(v);
}
}
cellindex++;
}
byte[] buffer = new byte[1024];
using (MemoryStream output = new MemoryStream())
{
workbook.Write(output);
buffer = output.ToArray();
}
return(buffer);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
}
}
/// <summary>
/// Excel转table 导入 Michaux 20160531
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public DataTable ImportExcelToDataTable(HttpPostedFileBase file,Dictionary<string,ExcelFormatter> formatColumn=null)
{
var datatable = new DataTable();
if (file.FileName.IndexOf("xlsx") > -1)
{
NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
//if (formatColumn!=null && formatColumn.ContainsKey(firstRow.GetCell(j).StringCellValue))
//{
//}
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
#region 清除最后的空行
for (int i = datatable.Rows.Count - 1; i > 0; i--)
{
bool isnull = true;
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (datatable.Rows[i][j] != null)
{
if (datatable.Rows[i][j].ToString() != "")
{
isnull = false;
break;
}
}
}
if (isnull)
{
datatable.Rows[i].Delete();
}
}
#endregion
return datatable;
}
public ZherebExcelImportForm(Db dbContext, Competition competition)
: this()
{
try
{
DbContext = dbContext;
Competition = competition;
DbContext.TeamSet.Load();
this.GridEX.RootTable.Columns["Team"].HasValueList = true;
this.GridEX.RootTable.Columns["Team"].EditType = EditType.Combo;
this.GridEX.RootTable.Columns["Team"].ColumnType = ColumnType.Text;
this.GridEX.RootTable.Columns["Team"].ValueList.PopulateValueList(DbContext.TeamSet.Local.OrderBy(f => f.Name).ToList(), "Name");
using (var fs = new OpenFileDialog()
{
Filter = "Excel (*.xl*)|*.xl*"
})
if (fs.ShowDialog(this) == DialogResult.OK)
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs.OpenFile());
var sheet = workbook.GetSheetAt(0);
var nameIndex = ExcelService.GetNameColumnIndex(sheet);
if (nameIndex < 0)
{
MessageBox.Show("Не могу найти столбец команд");
return;
}
ICell zherCell = null;
foreach (var c in sheet.GetRow(0).Cells.Where(f => f.CellType == CellType.String).Reverse())
{
switch (
MessageBox.Show(this, string.Format("Колонка '{0}' это номер жеребьевки?", c.StringCellValue), "выберите колонку", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question))
{
case System.Windows.Forms.DialogResult.Yes:
zherCell = c;
break;
case System.Windows.Forms.DialogResult.Cancel:
DialogResult = System.Windows.Forms.DialogResult.Cancel;
return;
break;
}
if (zherCell != null)
{
break;
}
}
if (zherCell == null)
{
DialogResult = System.Windows.Forms.DialogResult.Cancel;
return;
}
var names = new List <string>();
var data = new BindingList <ImportItem>();
foreach (var r in sheet)
{
var row = r as IRow;
if (row == null || row.RowNum == 0 || row.GetCell(nameIndex) == null || row.GetCell(nameIndex).CellType != CellType.String)
{
continue;
}
var name = row.GetCell(nameIndex).StringCellValue.Trim();
if (string.IsNullOrEmpty(name))
{
continue;
}
var alt = name + ";";
var team = DbContext.TeamSet.Local.FirstOrDefault(f => string.Compare(f.Name, name, true) == 0 || f.AlternativeNames.Contains(alt));
if (team == null)
{
MessageBox.Show(this, $"Файл содержит неизвестные команду '{name}'! Импорта не будет.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
DialogResult = System.Windows.Forms.DialogResult.Cancel;
return;
}
if (!team.Used)
{
MessageBox.Show(this, "Файл содержит команды без аккредитации! Импорта не будет.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
DialogResult = System.Windows.Forms.DialogResult.Cancel;
return;
}
data.Add(new ImportItem()
{
Team = team, Number = row.GetCell(zherCell.ColumnIndex) != null ? row.GetCell(zherCell.ColumnIndex).ToString().Trim() : ""
});
}
GridEX.DataSource = data;
}
else
{
DialogResult = System.Windows.Forms.DialogResult.Cancel;
}
}
catch (Exception ex)
{
MessageBox.Show(this, "Ошибка при импорте жеребьевки " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
DialogResult = DialogResult.Abort;
}
}
public DataTable ImportExcelBase(HttpPostedFileBase file, out Dictionary <int, PicturesInfo> ImgList, Dictionary <string, ExcelFormatter> formatColumn = null)
{
var datatable = new DataTable();
ImgList = new Dictionary <int, PicturesInfo>();
if (file.FileName.IndexOf("xlsx") > -1)
{
NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
bool imgForm = true;
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
if (formatColumn != null && formatColumn.Values.Where(x => x.ColumnName == firstRow.GetCell(j).StringCellValue).Any())
{
ExcelFormatter excelFormatter = formatColumn.Values.Where(x => x.ColumnName == firstRow.GetCell(j).StringCellValue).FirstOrDefault();
if (excelFormatter.ColumnTrans.Equals(EnumColumnTrans.ConvertImportImage))
{
if (imgForm)
{
ImgList = NPOIExtendImg.GetAllPictureInfos(sheet);
imgForm = false;
}
datarow[j] = GetImgsUrl(ImgList, i - 1);
continue;
}
}
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
#region 清除最后的空行
for (int i = datatable.Rows.Count - 1; i > 0; i--)
{
bool isnull = true;
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (datatable.Rows[i][j] != null)
{
if (datatable.Rows[i][j].ToString() != "")
{
isnull = false;
break;
}
}
}
if (isnull)
{
datatable.Rows[i].Delete();
}
}
#endregion
return(datatable);
}
