/// <summary>
/// IQC 导出Excel 数据流
/// </summary>
/// <param name="dataSource">数据源</param>
/// <returns></returns>
public System.IO.MemoryStream ExportPrintToExcel(List <SampleItemsIqcRecordModel> dataSource)
{
try
{
//数据为Null时返回数值
System.IO.MemoryStream stream = new System.IO.MemoryStream();
if (dataSource == null || dataSource.Count == 0)
{
return(stream);
}
string filePath = System.IO.Path.GetFullPath(PrintSampleModel(dataSource));
NPOI.HSSF.UserModel.HSSFWorkbook workbook = InitializeWorkbook(filePath);
if (workbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
sheet.ForceFormulaRecalculation = true;
//保存
Store(dataSource);
return(stream);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
private static DataSet ExcelToTable(string fileName)
{
DataSet ds = new DataSet();
DataTable dt = null;
FileStream fs = new FileStream(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++)
{
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.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
if (row.GetCell(j) != null)
{
sheet.GetRow(i).GetCell(j).SetCellType(NPOI.SS.UserModel.CellType.String);
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
}
ds.Tables.Add(dt);
}
return(ds);
}
private static DataTable ExcelInput(string FilePath)
{
//第一行一般为标题行。
DataTable table = new DataTable();
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档，然后关闭文件
FileStream fsRead = File.OpenRead(FilePath);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead);
fsRead.Close();
//获取excel的第一个sheet
NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
//获取Excel的最大行数
int rowsCount = sheet.PhysicalNumberOfRows;
//为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
//为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
//int colsCount = sheet.GetRow(1).PhysicalNumberOfCells;
//for (int i = 0; i < colsCount; i++)
//{
// table.Columns.Add(i.ToString());
//}
//for (int x = 0; x < rowsCount; x++)
//{
// DataRow dr = table.NewRow();
// for (int y = 0; y < colsCount; y++)
// {
// dr[y] = sheet.GetRow(x).GetCell(y).ToString();
// }
// table.Rows.Add(dr);
//}
//dataGridView标题
table.Columns.Add("原始值");
table.Columns.Add("修正值");
//取模板中第6列,从第二行开始循环取值
for (int x = 2; x < rowsCount; x++)
{
DataRow dr = table.NewRow();
dr[0] = sheet.GetRow(x).GetCell(6).ToString();
//第二列填入修改后的值
dr[1] = getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32);
table.Rows.Add(dr);
}
sheet = null;
workbook = null;
return(table);
}
/// <summary>
/// LoadFromTamplateFile
/// </summary>
/// <param name="FileName"></param>
/// <returns></returns>
public Boolean LoadFromTamplateFile(string FileName)
{
using (System.IO.FileStream fs = System.IO.File.OpenRead(FileName)) //打开myxls.xls文件
{
Book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
if (Book.NumberOfSheets > 0)
{
DefaultSheet = Book.GetSheetAt(0);
}
else
{
DefaultSheet = Book.CreateSheet("Sheet1");
}
}
return(true);
}
/// <summary>
/// Read an xls file from the specified path
/// </summary>
/// <param name="path">The path to the xls file</param>
public static List <List <string> > Read(string path)
{
var returnValues = new List <List <string> >();
try
{
NPOI.SS.UserModel.IWorkbook book;
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read))
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
}
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);
for (int rowIndex = sheet.FirstRowNum;
rowIndex <= sheet.LastRowNum; rowIndex++)
{
var row = sheet.GetRow(rowIndex);
List <string> currentRowList = new List <string>();
for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++)
{
var cell = row.GetCell(cellIndex);
if (cell != null)
{
currentRowList.Add(cell.StringCellValue);
}
else
{
currentRowList.Add(string.Empty);
}
}
returnValues.Add(currentRowList);
}
}
catch (Exception ex)
{
UnityEngine.Debug.LogError("Не удалось прочитать файл xls по пути: " + path + ", Ошибка: " + ex.Message);
}
return(returnValues);
}
private void buttonUpdateAndSaveFile_Click(object sender, EventArgs e)
{
if (!datasource.Equals("") && !excelsource.Equals(""))
{
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档，然后关闭文件
FileStream fsRead = File.OpenRead(excelsource);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead);
fsRead.Close();
//获取excel的第一个sheet
NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
//获取Excel的最大行数
int rowsCount = sheet.PhysicalNumberOfRows;
for (int x = 2; x < rowsCount; x++)
{
sheet.GetRow(x).GetCell(6).SetCellValue(getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32));
}
//把编辑过后的工作薄重新保存为excel文件
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "All files (*.*)|*.*|xls files (*.xls)|*.xls";
dialog.FilterIndex = 2;
dialog.RestoreDirectory = true;
dialog.ShowDialog();
if (!string.IsNullOrEmpty(dialog.FileName))
{
FileStream fsWrite = File.Create(@dialog.FileName);
workbook.Write(fsWrite);
fsWrite.Close();
}
}
else
{
MessageBox.Show("都不加载下数据就搞？重头来过！");
datasource = "";
excelsource = "";
textBoxExcelUrl.Text = "";
textBoxDbUrl.Text = "";
}
}
/// <summary>
/// 导入excel
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public ActionResult ImportExcel(HttpPostedFileBase file)
{
try
{
//思路：获取读取的文件，把文件转换为二进制数组，然后转成内存流，利用NPOI把内存流中的数据读取成Excel
Session.Remove("ImportExcel"); //把session中的ImportExcel移除避免残留以前数据
string fileExtension = System.IO.Path.GetExtension(file.FileName); //读取路径文件的扩展名
if (".xls".Equals(fileExtension) || ".XLS".Equals(fileExtension)) //判断读取的文件是.xls文件
{
byte[] fileBytes = new byte[file.ContentLength]; //指定数组的长度获取Excel数据的大小
file.InputStream.Read(fileBytes, 0, file.ContentLength); //读取文件内容
// 转为 内存流
System.IO.MemoryStream excelFileStream = new System.IO.MemoryStream(fileBytes);
//将内存流转为 工作簿
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelFileStream);
//判断工作簿中的工作表（Sheet）的个数
if (workbook.NumberOfSheets > 0)
{
//查询出 学院,专业,年级,班级 的信息：目的是用来查看导入的数据是否有重复
List <PatientVo> lsitStudentVos = new List <PatientVo>();
// 获取第一个工作表
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
//PhysicalNumberOfRows 获取的是物理行数，也就是不包括那些空行（隔行）的情况。
//判断 工作表（sheet)中有数据
if (sheet.PhysicalNumberOfRows > 0)
{
//将数据先装到datatable中
// 定义datatable
DataTable dtExcel = new DataTable();
//获取标题行 第一行
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
//获取一行单元格个数 LastCellNum 获取列数，比最后一列列标大 1
int cellCount = headerRow.LastCellNum;
//获取数据总行数 LastRowNum 最后一行行标，比行数小 1
int rowCount = sheet.LastRowNum + 1;
//创建DataTable的列Columns
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dtExcel.Columns.Add(column);
}
//读取Excel中的数据
//(sheet.FirstRowNum) 第一行是标题
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //获取行
DataRow dataRow = dtExcel.NewRow(); //DataTable创建一行
if (row != null)
{
//遍历Excel一行的所有单元格
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
//添加行DataRow到DataTable
dtExcel.Rows.Add(dataRow);
}
//遍历datatable 获取数据
foreach (DataRow row in dtExcel.Rows)
{
//创建一个 StudentVo的对象
PatientVo student = new PatientVo();
try
{
//获取性别ID
var dd = row["性别"].ToString().Trim();
var SexID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == dd select tbSex.BaseDetailID).Single();
student.sexID = Convert.ToInt32(SexID);
student.Sex = row["性别"].ToString().Trim();
//病人所属ID
var PatientOwnership = row["病人所属"].ToString().Trim();
var cantonID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == PatientOwnership select tbSex.BaseDetailID).Single();
student.cantonID = Convert.ToInt32(cantonID);
student.PatientOwnership = row["病人所属"].ToString().Trim();
//获取人群分类id和名称
var CrowdClass = row["人群分类"].ToString().Trim();
var CrowdclassificationID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CrowdClass select tbSex.BaseDetailID).Single();
student.CrowdclassificationID = Convert.ToInt32(CrowdclassificationID);
student.CrowdClass = row["人群分类"].ToString().Trim();
//获取病例分类ID和名称
var CaseClass = row["病例分类"].ToString().Trim();
var CaseClassID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CaseClass select tbSex.BaseDetailID).Single();
student.CaseClassID = Convert.ToInt32(CaseClassID);
student.CaseClass = row["病例分类"].ToString().Trim();
//获取病例名称ID和名称
var DiseaseType = row["疾病名称类型"].ToString().Trim();
var DiseaseTypeID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == DiseaseType select tbSex.BaseDetailID).Single();
student.DiseaseTypeID = Convert.ToInt32(DiseaseTypeID);
student.DiseaseType = row["疾病名称类型"].ToString().Trim();
//获取审核状态ID和名称
var AuditStatu = row["审核状态"].ToString().Trim();
var AuditStatusID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == AuditStatu select tbSex.BaseDetailID).Single();
student.AuditStatusID = Convert.ToInt32(AuditStatusID);
student.AuditStatus = row["审核状态"].ToString().Trim();
//给创建实体赋值
student.Cardnumber = row["卡片编号"].ToString().Trim();
student.name = row["患者姓名"].ToString().Trim();
student.IDnumber = row["身份证号码"].ToString().Trim();
student.BirthDateTime = row["出生日期"].ToString().Trim();
student.workunit = row["工作单位"].ToString().Trim();
student.Addressnationalstandard = row["现详细住址国标"].ToString().Trim();
student.Detailedaddress = row["现住详细地址"].ToString().Trim();
student.CensusAddressInter = row["户籍地址国标"].ToString().Trim();
student.CensusAddDetail = row["户籍地址详细"].ToString().Trim();
student.MorbidityDateTime = row["发病日期"].ToString().Trim();
student.DiagnoseDateTime = row["诊断日期"].ToString().Trim();
student.ReportDateTime = row["录入日期"].ToString().Trim();
student.RevisedDateTime = row["订正终审日期"].ToString().Trim();
student.Occupation = row["职业"].ToString().Trim();
student.Remark = row["备注"].ToString().Trim();
lsitStudentVos.Add(student);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//把数据存在session当中做临时保存，这里还没有保存到数据库
Session["ImportExcel"] = lsitStudentVos;
return(Json(true, JsonRequestBehavior.AllowGet));
}
else
{
//物理行数为0
}
}
else
{
//没有工作表
}
}
else
{
//上传的文件类型不正确
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(Json(false, JsonRequestBehavior.AllowGet));
}
public Form_PurchaseOrderImpt()
{
InitializeComponent();
#region 绑定类型
var ordertypes = EnumToListHelper.ConverEnumToList(typeof(PurchaseDrugTypes)).Where(r => r.Name != "食品").ToList();
this.toolStripComboBox1.ComboBox.DisplayMember = "Name";
this.toolStripComboBox1.ComboBox.ValueMember = "Id";
this.toolStripComboBox1.ComboBox.DataSource = ordertypes;
#endregion
#region DataGridView初始化
this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
this.dataGridView1.RowPostPaint += (s, e) => DataGridViewOperator.SetRowNumber(this.dataGridView1, e);
this.dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
this.dataGridView2.RowPostPaint += (s, e) => DataGridViewOperator.SetRowNumber(this.dataGridView2, e);
#endregion
#region 右键菜单
BugsBox.Pharmacy.UI.Common.BaseRightMenu brm = new BugsBox.Pharmacy.UI.Common.BaseRightMenu(this.dataGridView1);
#endregion
#region 清理表格数据
Action ClearData = () =>
{
this.ListDetails.Clear();
this.dataGridView1.DataSource = null;
this.ListDetailsWaitingImpt.Clear();
this.dataGridView2.DataSource = null;
};
#endregion
#region 打开EXCEL文件
this.toolStripButton1.Click += (s, e) =>
{
OpenFileDialog ofd = new OpenFileDialog
{
Filter = "XLS文件|*.xls|XLSX文件|*.xlsx",
};
var re = ofd.ShowDialog();
if (re != System.Windows.Forms.DialogResult.OK)
{
return;
}
FileStream fs = new FileStream(ofd.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);
#region 简单验证一下excel表格
if (sheet == null)
{
MessageBox.Show("模板文件出错，请检查！"); return;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
MessageBox.Show("模板文件出错，请检查！"); return;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
MessageBox.Show("模板文件出错，请检查！"); return;
}
#endregion
ClearData();
for (int i = 1; i < sheet.LastRowNum + 1; i++)
{
var sheetrow = sheet.GetRow(i);
Business.Models.PurchaseOrderImpt m = new Business.Models.PurchaseOrderImpt();
m.ProductGeneralName = sheet.GetRow(i).Cells[0].StringCellValue;
m.DosageName = sheet.GetRow(i).Cells[1].StringCellValue;
m.SpecificName = sheet.GetRow(i).Cells[2].StringCellValue;
m.MeasurementName = sheet.GetRow(i).Cells[3].StringCellValue;
m.FactoryName = sheet.GetRow(i).Cells[4].StringCellValue;
m.Origin = sheet.GetRow(i).Cells[5].StringCellValue;
m.Amount = decimal.Parse(sheet.GetRow(i).Cells[6].NumericCellValue.ToString());
m.UnitPrice = decimal.Parse(sheet.GetRow(i).Cells[7].NumericCellValue.ToString());
m.TaxRate = decimal.Parse(sheet.GetRow(i).Cells[8].NumericCellValue.ToString());
this.ListDetails.Add(m);
}
this.dataGridView1.DataSource = this.ListDetails;
this.dataGridView1.Columns["DruginfoId"].Visible = false;
};
#endregion
#region 生成模板文件
this.toolStripButton4.Click += (s, e) =>
{
DownlodExcel();
MessageBox.Show("导出成功！");
};
#endregion
#region 务器端验证
this.toolStripButton2.Click += (s, e) =>
{
if (this.dataGridView1.Rows.Count <= 0)
{
return;
}
var result = this.PharmacyDatabaseService.CheckForPurchaseOrderDetails(this.ListDetails, out msg).ToList();
if (result.Any(r => r.DrugInfoId == Guid.Empty))
{
MessageBox.Show("有一个或多个记录没有验证成功，请检查品名，剂型，规格等基本信息！您可以修改后再尝试验证！");
}
this.ListDetails = result.Where(r => r.DrugInfoId == Guid.Empty).ToList();
this.dataGridView1.DataSource = ListDetails;
this.ListDetailsWaitingImpt = result.Where(r => r.DrugInfoId != Guid.Empty).ToList();
this.dataGridView2.DataSource = this.ListDetailsWaitingImpt;
this.dataGridView2.Columns["DrugInfoId"].Visible = false;
this.dataGridView2.Refresh();
};
#endregion
#region 导入按钮click
this.toolStripButton3.Click += (s, e) =>
{
if (this.ListDetailsWaitingImpt.Count <= 0)
{
return;
}
if (this.OnPurchaseOrderImpt != null)
{
PurchaseOrderImptEventArgs args = new PurchaseOrderImptEventArgs
{
ImptList = this.ListDetailsWaitingImpt
};
this.OnPurchaseOrderImpt(args);
}
};
#endregion
}
public static DataSet parse(string ExcelFilePath, string ZipPassword, string FirstColumnName)
{
//OleDbConnection cnnxls = null;
DataSet ds = new DataSet();
if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".zip", StringComparison.CurrentCultureIgnoreCase))
{
string strTmpFolder = ExcelFilePath + ".dir";
try
{
zip.ExtractAll(ExcelFilePath, strTmpFolder, ZipPassword);
System.IO.DirectoryInfo rootDir = new System.IO.DirectoryInfo(strTmpFolder);
foreach (System.IO.FileInfo fileInfo in rootDir.GetFiles("*", System.IO.SearchOption.AllDirectories))
{
ds.Merge(parse(fileInfo.FullName, ZipPassword, FirstColumnName));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.IO.Directory.Delete(strTmpFolder, true);
}
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFolder + ";Extended Properties=\"text;HDR=YES;IMEX=1;FMT=Delimited;\"";
//cnnxls = new OleDbConnection(strConn);
//cnnxls.Open();
}
else if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".csv", StringComparison.CurrentCultureIgnoreCase))
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(ExcelFilePath);
DataTable table = CSVReader.parse(fileInfo.OpenRead(), true, ",", "\"");
table.TableName = System.IO.Path.GetFileNameWithoutExtension(fileInfo.FullName);
ds.Tables.Add(table);
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(ExcelFilePath, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename);
for (int sheetIndex = 0; sheetIndex < workBook.NumberOfSheets; sheetIndex++)
{
if (!workBook.IsSheetHidden(sheetIndex))
{
int intHeaderRow = 0;
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(sheetIndex);
NPOI.HSSF.UserModel.HSSFRow headerRow = null; //= (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow);
if (!string.IsNullOrEmpty(FirstColumnName))
{
for (int tmpRowIdx = intHeaderRow; tmpRowIdx <= workSheet.LastRowNum; tmpRowIdx++)
{
headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(tmpRowIdx);
if (headerRow == null)
{
continue;
}
bool columnNameMatch = false;
for (int tmpColumnIndex = 0; tmpColumnIndex <= headerRow.LastCellNum; tmpColumnIndex++)
{
if (headerRow.GetCell(tmpColumnIndex) != null)
{
string columnName = headerRow.GetCell(tmpColumnIndex).ToString().Trim();
if (FirstColumnName.Equals(columnName))
{
intHeaderRow = tmpRowIdx;
columnNameMatch = true;
break;
}
}
}
if (columnNameMatch)
{
break;
}
}
}
else
{
headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow);
}
if (headerRow == null)
{
continue;
}
string tableName = workSheet.SheetName.Trim();
DataTable table = new DataTable(tableName);
int intColumnIndex = 0;
while (intColumnIndex <= headerRow.LastCellNum)
{
if (headerRow.GetCell(intColumnIndex) != null)
{
string columnName = headerRow.GetCell(intColumnIndex).ToString().Trim();
if (string.IsNullOrEmpty(columnName))
{
columnName = "Column_" + intColumnIndex;
}
if (table.Columns.Contains(columnName))
{
columnName = "Column_" + intColumnIndex;
}
table.Columns.Add(columnName, typeof(string));
// resign new value of column name to Excel for below part of import
headerRow.GetCell(intColumnIndex).SetCellValue(columnName);
}
intColumnIndex++;
}
int rowCount = 1;
while (intHeaderRow + rowCount <= workSheet.LastRowNum)
{
int colCount = 0;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow + rowCount);
if (row == null)
{
rowCount++;
continue;
}
DataRow dataRow = table.NewRow();
while (colCount <= headerRow.LastCellNum)
{
if (headerRow.GetCell(colCount) != null)
{
string columnName = headerRow.GetCell(colCount).ToString();
if (table.Columns.Contains(columnName))
{
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(colCount);
if (cell != null)
{
if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.FORMULA))
{
NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workBook);
cell = (NPOI.HSSF.UserModel.HSSFCell)e.EvaluateInCell(cell);
}
string fieldValue = cell.ToString();
if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
string format = string.Empty;
//bool IsBuildinformat = false;
// Not sure whether workBook.CreateDataFormat().GetFormat(index) can obtain all the build-in format
try
{
format = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat(cell.CellStyle.DataFormat);
//IsBuildinformat = true;
}
catch
{
format = workBook.CreateDataFormat().GetFormat(cell.CellStyle.DataFormat);
}
// [h]:mm:ss handle NOT support
int midBlanketStartPos = format.IndexOf('[');
while (midBlanketStartPos >= 0)
{
int midBlanketEndPos = format.IndexOf(']', midBlanketStartPos);
format = format.Substring(0, midBlanketStartPos) + format.Substring(midBlanketStartPos + 1, midBlanketEndPos - midBlanketStartPos - 1) + format.Substring(midBlanketEndPos + 1);
midBlanketStartPos = format.IndexOf('[');
}
if (format.IndexOf("y", StringComparison.CurrentCultureIgnoreCase) >= 0 || format.IndexOf("d", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
DateTime date = cell.DateCellValue;
if (date.TimeOfDay.TotalSeconds > 0)
{
fieldValue = date.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
fieldValue = date.ToString("yyyy-MM-dd");
}
}
}
else if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
DateTime date = cell.DateCellValue;
// default date of "Time Only" field is 1899-12-31
if (!date.Date.Ticks.Equals(new DateTime(1899, 12, 31).Ticks))
{
fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
fieldValue = cell.DateCellValue.ToString("HH:mm:ss");
}
}
else
{
fieldValue = cell.NumericCellValue.ToString();
}
}
dataRow[columnName] = fieldValue;
}
}
}
colCount++;
}
table.Rows.Add(dataRow);
rowCount++;
}
ds.Tables.Add(table);
}
}
//string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\"";
//cnnxls = new OleDbConnection(strConn);
//try
//{
// cnnxls.Open();
//}
//catch
//{
// cnnxls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\"";
// cnnxls.Open();
//}
//DataTable schemaTable = cnnxls.GetSchema("Tables");
//foreach (DataRow schemaRow in schemaTable.Rows)
//{
// string tableName = schemaRow["Table_Name"].ToString().Trim();
// if (tableName.EndsWith("$"))
// {
// OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", cnnxls);
// try
// {
// //DataTable[] tables = oda.FillSchema(ds, SchemaType.Mapped);//
// //tables[0].TableName = schemaRow["Table_Name"].ToString().Replace("$", "").Replace("#csv", "");
// //if (tables[0].Columns.Contains("Emp No*"))
// // tables[0].Columns["Emp No*"].DataType = typeof(string);
// //OleDbDataReader dr = oda.SelectCommand.ExecuteReader();
// //while (dr.Read())
// //{
// // DataRow row = tables[0].NewRow();
// // for (int i = 0; i < tables[0].Columns.Count; i++)
// // row[i] = dr[i];
// // tables[0].Rows.Add(row);
// //}
// //// oda.Fill(tables[0]);
// //if (ds.Tables.Contains(tableName) && tableName.ToString().EndsWith("$"))
// // ds.Tables.Remove(tableName);
// string actualTableName = tableName.Substring(0, tableName.Length - 1);
// if (!ds.Tables.Contains(actualTableName))
// oda.Fill(ds, actualTableName);
// }
// catch
// {
// // unknown error caused by hidden sheet
// }
// // oda.Fill(ds);
// }
//}
//cnnxls.Close();
}
foreach (DataTable tempTable in ds.Tables)
{
for (int rowIdx = tempTable.Rows.Count - 1; rowIdx >= 0; rowIdx--)
{
DataRow row = tempTable.Rows[rowIdx];
bool isEmptyRow = true;
foreach (DataColumn tempColumn in tempTable.Columns)
{
if (!row.IsNull(tempColumn))
{
if (!string.IsNullOrEmpty(row[tempColumn].ToString().Trim()))
{
isEmptyRow = false;
break;
}
}
}
if (isEmptyRow)
{
tempTable.Rows.Remove(row);
}
else
{
break;
}
}
}
foreach (DataTable tempTable in ds.Tables)
{
foreach (DataColumn tempColumn in tempTable.Columns)
{
string tempColumnName = tempColumn.ColumnName;
tempColumnName = tempColumnName.Trim().Replace("*", "");
tempColumnName = tempColumnName.Trim().Replace("#", "");
tempColumn.ColumnName = tempColumnName;
}
}
return(ds);
}
public override FileInfo GenerateBankFile()
{
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(System.IO.File.OpenRead(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ICBCBankFile.xls")));
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0);
int rowCount = 10;
foreach (GenericBankFileDetail bankFileDetail in BankFileDetails)
{
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowCount);
if (row == null)
{
row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowCount);
}
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(0);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
}
cell.SetCellValue(bankFileDetail.EmpNo);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(1);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1);
}
cell.SetCellValue(bankFileDetail.EmpBankAccountHolderName);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(2);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2);
}
cell.SetCellValue(bankFileDetail.BankCode + bankFileDetail.BranchCode + bankFileDetail.AccountNo);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(3);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3);
}
cell.SetCellValue(bankFileDetail.Amount);
rowCount++;
}
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workBook.Write(file);
file.Close();
return(new FileInfo(exportFileName));
}
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];
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">DataTable：要导出的数据源</param>
/// <param name="excelFilePath">string：文件的物理路径</param>
/// <param name="isCustomHead">bool：是否自定义表头</param>
/// <param name="tbColumnNames">List:表头</param>
/// <param name="sheetName">string：sheet表名</param>
/// <returns></returns>
public static bool Output(DataTable dt, string excelFilePath, bool isCustomHead = false, List <string> tbColumnNames = null, string sheetName = "", Action <Exception> errorFun = null, int sheetIndex = 0)
{
if (!System.IO.File.Exists(excelFilePath))
{
throw new Exception("Excel 文件不存在");
}
if (null == dt && dt.Rows.Count == 0)
{
return(false);
}
try
{
//1.0 创建工作薄 和 工作表对象
NPOI.HSSF.UserModel.HSSFWorkbook book;
using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite))
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile);
}
sheetName = string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName;
NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表
if (null == sheet1)
{
sheet1 = book.CreateSheet(sheetName);
}
else if (sheet1.SheetName != sheetName)
{
book.SetSheetName(sheetIndex, sheetName);
}
//2.0给sheet1添加第一行的头部标题
if (!isCustomHead || tbColumnNames == null || tbColumnNames.Count != dt.Columns.Count)
{
tbColumnNames = new List <string>();
foreach (DataColumn item in dt.Columns)
{
tbColumnNames.Add(item.ColumnName);
}
}
NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行
for (int i = 0; i < tbColumnNames.Count; i++)
{
rowHead.CreateCell(i).SetCellValue(tbColumnNames[i]);
}
//3.0 填充表格数据
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1); //创建数据行
for (int j = 0; j < dt.Columns.Count; j++) //填充行数据
{
rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//4.0 写入文件
using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create))
{
book.Write(wfile);
}
}
catch (Exception ex)
{
if (errorFun != null)
{
errorFun(ex);
}
return(false);
}
return(true);
}
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;
}
}
public string UpLoadFile()
{
try
{
var Filename = PostObjectDateValue("Filename");
//var File = PostObjectValue("File");
DateTime dateTime = Convert.ToDateTime(Filename);
string year = dateTime.Year.ToString();
string month = dateTime.Month.ToString();
string day = dateTime.Day.ToString();
string filename = year + "-" + month + "-" + day;
string tagpath = @"C:\Users\HK\Desktop\" + filename + @"(Mapping)\";
string listfilepath = @"C:\Users\HK\Desktop\" + filename + @"(Mapping)\List.xls";
List <MailInfo> MailInfoList = new List <MailInfo>();
List <string> FailedList = new List <string>();
// DataSet ds = new DataSet();
//DataTable dt = null;
NPOI.HSSF.UserModel.HSSFWorkbook book;
try
{
FileStream fs = new FileStream(listfilepath, FileMode.Open, FileAccess.Read);
book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
catch (Exception e)
{
return(GetJsonString("Error", "打开文件错误!" + e));
}
DateTime now = DateTime.Now;
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 = 7;
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));
//}
try
{
for (int i = 0; i <= sheet.LastRowNum; i++)
{
//DataRow newRow = dt.Rows.Add();
MailInfo mailInfo = new MailInfo();
mailInfo.F_UploadDate = now.Date;
mailInfo.F_IshaveFile = 0;
for (int j = firstCellNum; j < lastCellNum; j++)
{
//newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
if (j == firstCellNum)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
mailInfo.CompanyName = (sheet.GetRow(i).GetCell(j).ToString().Trim());
}
else
{
mailInfo.CompanyName = "";
}
if (mailInfo.CompanyName[mailInfo.CompanyName.Length - 1] == '.')
{
mailInfo.CompanyName = mailInfo.CompanyName.Substring(0, mailInfo.CompanyName.Length - 1);
}
}
else if (j == firstCellNum + 1)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
mailInfo.F_FaildReason = sheet.GetRow(i).GetCell(j).ToString();
}
else
{
mailInfo.F_FaildReason = "";
}
}
else if (j == firstCellNum + 2)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
mailInfo.F_ClassName = sheet.GetRow(i).GetCell(j).ToString().Replace(".", "");
}
else
{
mailInfo.F_ClassName = "";
}
}
else if (j == firstCellNum + 3)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
mailInfo.F_DealType = sheet.GetRow(i).GetCell(j).ToString();
}
else
{
mailInfo.F_DealType = "";
}
}
else if (j == firstCellNum + 4)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
mailInfo.F_DealResult = sheet.GetRow(i).GetCell(j).ToString();
}
else
{
mailInfo.F_DealResult = "";
}
}
else if (j == firstCellNum + 5)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
mailInfo.F_FileEndingDate = sheet.GetRow(i).GetCell(j).ToString().ToDateTime().Date;
}
else
{
mailInfo.F_FileEndingDate = DateTime.MinValue;
}
}
else if (j == firstCellNum + 6)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
mailInfo.F_ChangeDetails = sheet.GetRow(i).GetCell(j).ToString();
}
else
{
mailInfo.F_ChangeDetails = "";
}
}
}
if (mailInfo.F_ClassName.Length > 3)
{
if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("XLS") != -1)
{
mailInfo.F_FileType = 1;
}
else if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("PDF") != -1)
{
mailInfo.F_FileType = 2;
}
else if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("XML") != -1)
{
mailInfo.F_FileType = 3;
}
else if (mailInfo.F_ClassName.Substring(0, 3).ToUpper().IndexOf("CVS") != -1)
{
mailInfo.F_FileType = 4;
}
else
{
mailInfo.F_FileType = 0;
}
}
if (mailInfo.F_DealResult.ToUpper().IndexOf("PENDING") != -1)
{
mailInfo.F_FileType = -1;
mailInfo.F_ClassName = "";
}
MailInfoList.Add(mailInfo);
}
}
catch
{
}
//ds.Tables.Add(dt);
}
//SqlConnection conn = new SqlConnection(SqlHelper.MyCONNECTSTRING);
if (MailInfoList.Count > 0)
{
try
{
string filepath = tagpath;
filepath = tagpath + "Debug";
List <FilePath> DebugFilelist = new List <FilePath>();
string[] diarrdebug = Directory.GetDirectories(@filepath, "*", SearchOption.AllDirectories);
for (int i = 0; i < diarrdebug.Length; i++)
{
string[] rootfilelist = System.IO.Directory.GetFiles(diarrdebug[i]);
List <File> filelist = new List <File>();
for (int j = 0; j < rootfilelist.Length; j++)
{
if (rootfilelist[j].Contains("rar") || rootfilelist[j].ToUpper().IndexOf("MAIL.TXT") != -1)
{
continue;
}
else
{
File file = new File();
file.Path = rootfilelist[j];
file.Type = rootfilelist[j].Substring(rootfilelist[j].LastIndexOf(".") + 1).ToUpper();
file.isbeenread = false;
filelist.Add(file);
}
}
FilePath filePath = new FilePath();
filePath.Path = diarrdebug[i];
filePath.ChildPath = filelist;
DebugFilelist.Add(filePath);
}
filepath = tagpath + "New";
List <FilePath> NewFilelist = new List <FilePath>();
string[] diarrnew = Directory.GetDirectories(@filepath, "*", SearchOption.AllDirectories);
for (int i = 0; i < diarrnew.Length; i++)
{
string[] rootfilelist = Directory.GetFiles(diarrnew[i]);
List <File> filelist = new List <File>();
for (int j = 0; j < rootfilelist.Length; j++)
{
if (rootfilelist[j].Contains("rar") || rootfilelist[j].ToUpper().IndexOf("MAIL.TXT") != -1)
{
continue;
}
else
{
File file = new File();
file.Path = rootfilelist[j];
file.Type = rootfilelist[j].Substring(rootfilelist[j].LastIndexOf(".") + 1).ToUpper();
file.isbeenread = false;
filelist.Add(file);
}
}
FilePath filePath = new FilePath();
filePath.Path = diarrnew[i];
filePath.ChildPath = filelist;
NewFilelist.Add(filePath);
}
filepath = tagpath + "Renew";
List <FilePath> RenewFilelist = new List <FilePath>();
string[] diarrrenew = Directory.GetDirectories(@filepath, "*", SearchOption.AllDirectories);
for (int i = 0; i < diarrrenew.Length; i++)
{
string[] rootfilelist = System.IO.Directory.GetFiles(diarrrenew[i]);
List <File> filelist = new List <File>();
for (int j = 0; j < rootfilelist.Length; j++)
{
if (rootfilelist[j].Contains("rar") || rootfilelist[j].ToUpper().IndexOf("MAIL.TXT") != -1)
{
continue;
}
else
{
File file = new File();
file.Path = rootfilelist[j];
file.Type = rootfilelist[j].Substring(rootfilelist[j].LastIndexOf(".") + 1).ToUpper();
file.isbeenread = false;
filelist.Add(file);
}
}
FilePath filePath = new FilePath();
filePath.Path = diarrrenew[i];
filePath.ChildPath = filelist;
RenewFilelist.Add(filePath);
}
foreach (var item in MailInfoList)
{
item.CompanyID = GetCompanyIdbyNameDB(item.CompanyName);
item.F_LastModifiedTime = DateTime.Now;
item.F_LastModifiedUser = "******";
long InsertId = InserttotableDB(item);
if (InsertId > 0)
{
//插入到另一个数据表中
MailData mailData = new MailData
{
EmailFID = InsertId,
F_UploadDate = item.F_UploadDate,
F_LastModifiedDate = DateTime.Now,
};
if (item.F_DealType.ToUpper().IndexOf("DEBUG") != -1)
{
var fileTypeAndData = GetFileDataAndType(item, DebugFilelist);
mailData.F_FileData = fileTypeAndData.Data;
mailData.F_FileType = fileTypeAndData.Type;
}
else if (item.F_DealType.ToUpper() == "NEW")
{
var fileTypeAndData = GetFileDataAndType(item, NewFilelist);
mailData.F_FileData = fileTypeAndData.Data;
mailData.F_FileType = fileTypeAndData.Type;
}
else if (item.F_DealType.ToUpper() == "RENEW")
{
var fileTypeAndData = GetFileDataAndType(item, RenewFilelist);
mailData.F_FileData = fileTypeAndData.Data;
mailData.F_FileType = fileTypeAndData.Type;
}
mailData.F_LastModifiedUser = "******";
if (mailData.F_FileData != null)
{
long FID = DataInsertToTableDB(mailData);
if (FID > 0)
{
UpdateishavefileDB(InsertId, FID);
}
}
else
{
FailedList.Add(item.CompanyName + "未找到文件，请检查");
}
}
}
//===========================================BulkInsert========================================================
//SqlBulkCopy SqlBulkCopy =new SqlBulkCopy( SqlHelper.MyCONNECTSTRING);
//SqlBulkCopy.DestinationTableName = "IridianDev2.dbo.OnlieMapping";
//DataTable dt = ToDataTable(MailInfoList);
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// SqlBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
//}
//SqlBulkCopy.WriteToServer(dt);
//===========================================BulkInsert========================================================
}
catch (Exception ex)
{
return(GetJsonString("Error", ex.Message));
}
finally
{
// conn.Dispose();
}
return(GetJsonString("Result", MailInfoList.Count, "Info", FailedList));
}
return(GetJsonString("Error", "本次提交的文件中没有数据."));
}
catch (Exception ex)
{
return(GetJsonString("Error", ex.Message));
}
}
/// <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;
}
private static DataTable Convert(string filePath)
{
using (FileStream stream = new FileStream(ServerHelper.MapPath(filePath), FileMode.Open))
{
string sheetName = "Sheet1";
DataTable data = new DataTable();
ISheet sheet = null;
int startRow = 0;
try
{
IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
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)
{
return(null);
}
}
}
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword)
{
ClearTempTable();
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Filename, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename);
NPOI.HSSF.UserModel.HSSFSheet workSheet = null;
//foreach (ExcelLibrary.SpreadSheet.Worksheet tmpWorkSheet in workBook.Worksheets)
//{
// if (tmpWorkSheet.Name.Trim().Equals("RosterTable"))
// {
// workSheet = tmpWorkSheet;
// break;
// }
//}
try
{
workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheet("RosterTable");
if (workSheet == null)
{
workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0);
}
}
catch
{
if (workSheet == null)
{
workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0);
}
}
if (workSheet.GetRow(ROW_YEAR).GetCell(0).StringCellValue.Trim().StartsWith("Year", StringComparison.CurrentCultureIgnoreCase))
{
if (workSheet.GetRow(ROW_YEAR).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
m_importYear = Convert.ToInt32(workSheet.GetRow(ROW_YEAR).GetCell(1).NumericCellValue);
}
else
{
errors.addError("Invalid Year", null);
}
}
if (workSheet.GetRow(ROW_MONTH).GetCell(0).StringCellValue.Trim().StartsWith("Month", StringComparison.CurrentCultureIgnoreCase))
{
if (workSheet.GetRow(ROW_MONTH).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
m_importMonth = Convert.ToInt32(workSheet.GetRow(ROW_MONTH).GetCell(1).NumericCellValue);
}
else
{
errors.addError("Invalid Month", null);
}
//if (!int.TryParse(workSheet.GetRow(1).GetCell(1).StringCellValue.Trim(), out m_importMonth))
// errors.addError("Invalid Month", null);
}
if (errors.List.Count > 0)
{
throw (new HRImportException(errors.Message()));
}
int intHeaterRow = ROW_CALENDAR_HEADER;
int intEmpColumn = 0;
ArrayList results = new ArrayList();
int rowCount = 1;
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow);
while (intEmpColumn <= headerRow.LastCellNum)
{
if (headerRow.GetCell(intEmpColumn) != null)
{
if (headerRow.GetCell(intEmpColumn).StringCellValue.Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase))
{
break;
}
}
intEmpColumn++;
}
if (intEmpColumn > headerRow.LastCellNum)
{
// do exception
}
int intEmptyEmpNoCount = 0;
try
{
while (intHeaterRow + rowCount <= workSheet.LastRowNum)
{
int EmpID = 0;
string EmpNo = string.Empty;
int colCount = 0;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow + rowCount);
if (row == null)
{
rowCount++;
intEmptyEmpNoCount++;
continue;
}
if (row.GetCell(intEmpColumn) == null)
{
rowCount++;
intEmptyEmpNoCount++;
continue;
}
EmpNo = row.GetCell(intEmpColumn).ToString().Trim();
if (string.IsNullOrEmpty(EmpNo))
{
rowCount++;
intEmptyEmpNoCount++;
continue;
}
intEmptyEmpNoCount = 0;
EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID);
if (EmpID < 0)
{
errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
}
else if (EmpID == 0)
{
errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
}
colCount = intEmptyEmpNoCount + 1;
int lastImportDay = 0;
int intCurrentMonth = m_importMonth;
int intCurrentYear = m_importYear;
while (colCount < headerRow.LastCellNum)
{
if (headerRow.GetCell(colCount) == null)
{
if (lastImportDay <= 0)
{
colCount++;
continue;
}
else
{
break;
}
}
if (headerRow.GetCell(colCount).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
int day = Convert.ToInt32(headerRow.GetCell(colCount).NumericCellValue);
if (day > 0 && day <= DateTime.DaysInMonth(intCurrentYear, intCurrentMonth))
{
if (lastImportDay > day)
{
intCurrentMonth++;
if (intCurrentMonth > 12)
{
intCurrentMonth = 1;
intCurrentYear++;
}
}
lastImportDay = day;
string RosterCode;
if (row.GetCell(colCount) != null)
{
RosterCode = row.GetCell(colCount).ToString().Trim();
}
else
{
RosterCode = string.Empty;
}
EUploadRosterTable uploadRosterTable = new EUploadRosterTable();
uploadRosterTable.EmpID = EmpID;
uploadRosterTable.RosterTableDate = new DateTime(intCurrentYear, intCurrentMonth, day);
DateTime inTime, outTime;
uploadRosterTable.RosterCodeID = Import.Parse.GetRosterCodeID(dbConn, RosterCode, out inTime, out outTime);
if (!inTime.Ticks.Equals(0))
{
uploadRosterTable.RosterTableOverrideInTime = inTime;
}
if (!outTime.Ticks.Equals(0))
{
uploadRosterTable.RosterTableOverrideOutTime = outTime;
}
uploadRosterTable.SessionID = m_SessionID;
uploadRosterTable.TransactionDate = UploadDateTime;
uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE;
if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode))
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() });
}
else
{
EUploadRosterTable.db.insert(dbConn, uploadRosterTable);
}
}
}
colCount++;
}
rowCount++;
}
}
catch (Exception e)
{
errors.addError(e.Message, null);
}
if (errors.List.Count > 0)
{
ClearTempTable();
throw (new HRImportException(errors.Message()));
}
return(GetImportDataFromTempDatabase(null));
//org.in2bits.MyXls.XlsDocument xlsDoc = new org.in2bits.MyXls.XlsDocument(Filename);
//org.in2bits.MyXls.Worksheet workSheet;
//try
//{
// workSheet = xlsDoc.Workbook.Worksheets["RosterTable"];
//}
//catch
//{
// workSheet = xlsDoc.Workbook.Worksheets[0];
//}
//if (workSheet.Rows[1].GetCell(1).Value.ToString().Trim().Equals("Year", StringComparison.CurrentCultureIgnoreCase))
//{
// if (!int.TryParse(workSheet.Rows[1].CellAtCol(2).Value.ToString().Trim(), out m_importYear))
// errors.addError("Invalid Year", null);
//}
//if (workSheet.Rows[2].GetCell(1).Value.ToString().Trim().Equals("Month", StringComparison.CurrentCultureIgnoreCase))
//{
// if (!int.TryParse(workSheet.Rows[2].CellAtCol(2).Value.ToString().Trim(), out m_importMonth))
// errors.addError("Invalid Month", null);
//}
//if (errors.List.Count > 0)
//{
// throw (new HRImportException(errors.Message()));
//}
//ushort intHeaderRow = 3;
//ArrayList results = new ArrayList();
//ushort rowCount = 1;
//try
//{
// while (intHeaderRow + rowCount <= workSheet.Rows.MaxRow)
// {
// int EmpID = 0;
// string EmpNo = string.Empty;
// ushort colCount = 1;
// org.in2bits.MyXls.Row row = workSheet.Rows[(ushort)(intHeaderRow + rowCount)];
// while (colCount <= row.MaxCellCol)
// {
// if (workSheet.Rows[intHeaderRow].CellAtCol(colCount).Value.ToString().Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase))
// {
// EmpNo = row.CellAtCol(colCount).Value.ToString().Trim();
// EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID);
// if (EmpID <= 0)
// errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
// break;
// }
// colCount++;
// }
// colCount = 1;
// while (colCount <= workSheet.Rows[(ushort)(intHeaderRow + rowCount)].MaxCellCol)
// {
// if (workSheet.Cells .Rows[(ushort)(intHeaderRow)].CellExists(colCount))
// {
// string RosterCode = string.Empty;
// if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellExists(colCount))
// if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellAtCol(colCount).Value != null)
// RosterCode = row.CellAtCol(colCount).Value.ToString().Trim();
// int day = 0;
// if (int.TryParse(workSheet.Rows[intHeaderRow].GetCell(colCount).Value.ToString().Trim(), out day))
// {
// if (day > 0 && day <= DateTime.DaysInMonth(m_importYear, m_importMonth))
// {
// EUploadRosterTable uploadRosterTable = new EUploadRosterTable();
// uploadRosterTable.EmpID = EmpID;
// uploadRosterTable.RosterTableDate = new DateTime(m_importYear, m_importMonth, day);
// uploadRosterTable.RosterCodeID = Import.Parse.*ID(dbConn, RosterCode);
// uploadRosterTable.SessionID = m_SessionID;
// uploadRosterTable.TransactionDate = UploadDateTime;
// uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE;
// if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode))
// {
// errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() });
// }
// else
// EUploadRosterTable.db.insert(dbConn, uploadRosterTable);
// }
// }
// }
// colCount++;
// }
// rowCount++;
// }
//}
//catch (Exception e)
//{
// errors.addError(e.Message, null);
//}
//if (errors.List.Count > 0)
//{
// ClearTempTable();
// throw (new HRImportException(errors.Message()));
//}
//return GetImportDataFromTempDatabase(null);
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
const string FIELD_COMPANY = "Company";
const string FIELD_POSITION = "Position";
const string FIELD_PAYROLLGROUP = "Payroll Group";
const string FIELD_EMPNO = "EmployeeID";
const string FIELD_EMPENGFULLNAME = "English Name";
const string FIELD_CHINESENAME = "¤¤¤å©m¦W";
const string FIELD_HKID = @"HKID/Passport";
const string FIELD_PERIODFROM = "From";
const string FIELD_PERIODTO = "To";
const string FIELD_WAGESWORK = "Wages Paid";
const string FIELD_WORKHOURTOTAL = "Total Working Hours";
const string FIELD_RESTDAYTOTAL = "No. of Rest Day";
const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH";
const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave";
const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave";
ArrayList list = new ArrayList();
foreach (RepeaterItem i in Repeater.Items)
{
CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
if (cb.Checked)
{
EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject();
WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb);
list.Add(o);
}
}
ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList();
if (list.Count > 0 && payPeriodList.Count > 0)
{
//const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] ";
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true);
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
DataSet dataSet = new DataSet(); //export.GetDataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
dataTable.Columns.Add(FIELD_COMPANY, typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add(FIELD_POSITION, typeof(string));
dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string));
dataTable.Columns.Add(FIELD_EMPNO, typeof(string));
dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string));
dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string));
dataTable.Columns.Add(FIELD_HKID, typeof(string));
dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime));
dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime));
dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double));
dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double));
dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double));
int firstSummaryColumnPos = dataTable.Columns.Count;
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
foreach (EEmpPersonalInfo empInfo in list)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID);
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID));
empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID));
if (PayrollStatus.SelectedValue.Equals("T"))
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T"));
}
else
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T"));
}
DataRow row = dataTable.NewRow();
row[FIELD_EMPNO] = empInfo.EmpNo;
row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName;
row[FIELD_CHINESENAME] = empInfo.EmpChiFullName;
row[FIELD_HKID] = empInfo.EmpHKID;
row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr;
row[FIELD_PERIODTO] = payPeriod.PayPeriodTo;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row[FIELD_COMPANY] = company.CompanyCode;
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row[FIELD_POSITION] = position.PositionDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
EPayrollGroup curentPayGroup = new EPayrollGroup();
curentPayGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, curentPayGroup))
{
row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc;
}
}
double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0;
double mcER = 0, mcEE = 0;
double vcER = 0, vcEE = 0;
double pFundER = 0, pFundEE = 0;
double wagesByWork = 0;
double wagesByRest = 0;
double fullPaidLeaveDays = 0;
double nonFullPaidLeaveDays = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
// Always Use Payment Code Description for grouping payment code with same description
//string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc;
//if (dataTable.Columns[fieldName] == null)
// dataTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
//if (row[fieldName] == null || row[fieldName] == DBNull.Value)
// row[fieldName] = 0;
//row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
if (payCode.PaymentCodeIsMPF)
{
releventIncome += paymentRecord.PayRecActAmount;
}
else
{
nonRelevantIncome += paymentRecord.PayRecActAmount;
}
DBFilter taxPaymentMapFilter = new DBFilter();
taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID));
if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0)
{
taxableAmount += paymentRecord.PayRecActAmount;
}
else
{
nonTaxableAmount += paymentRecord.PayRecActAmount;
}
if (payCode.PaymentCodeIsWages)
{
if (paymentRecord.PayRecIsRestDayPayment)
{
wagesByRest += paymentRecord.PayRecActAmount;
}
else
{
wagesByWork += paymentRecord.PayRecActAmount;
}
}
}
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
vcER += mpfRecord.MPFRecActVCER;
mcER += +mpfRecord.MPFRecActMCER;
vcEE += mpfRecord.MPFRecActVCEE;
mcEE += mpfRecord.MPFRecActMCEE;
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
pFundER += orsoRecord.ORSORecActER;
pFundEE += orsoRecord.ORSORecActEE;
}
row[FIELD_WAGESWORK] = wagesByWork;
DBFilter workingSummaryFilter = new DBFilter();
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo));
}
workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter);
double workHourTotal = 0, restDayTotal = 0;
foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList)
{
workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours;
restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled;
}
row[FIELD_WORKHOURTOTAL] = workHourTotal;
row[FIELD_RESTDAYTOTAL] = restDayTotal;
DBFilter statutoryHolidayFilter = new DBFilter();
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo));
}
ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter);
double restDayCount = 0;
foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList)
{
restDayCount++;
}
row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount;
DBFilter LeaveAppEmpPayrollFilter = new DBFilter();
LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter);
foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists)
{
ELeaveCode leaveCode = new ELeaveCode();
leaveCode.LeaveCodeID = leaveApp.LeaveCodeID;
if (ELeaveCode.db.select(dbConn, leaveCode))
{
if (leaveCode.LeaveCodePayRatio >= 1)
{
fullPaidLeaveDays += leaveApp.LeaveAppDays;
}
else
{
nonFullPaidLeaveDays += leaveApp.LeaveAppDays;
}
}
}
row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays;
row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays;
dataTable.Rows.Add(row);
}
}
}
//DBFilter paymentCodeFilter = new DBFilter();
//paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
//paymentCodeFilter.add("PaymentCode", false);
//ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
//foreach (EPaymentCode paymentCode in paymentCodeList)
//{
// if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc))
// {
// DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc];
// paymentColumn.SetOrdinal(firstDetailColumnPos);
// if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc))
// paymentColumn.ColumnName = paymentCode.PaymentCodeDesc;
// else
// {
// Console.Write("System reserved payment column is used");
// }
// }
//}
//for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
// dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
export.Update(dataSet);
System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream);
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1);
NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0);
if (excelRow == null)
{
excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
}
NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0);
if (excelCell == null)
{
excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0);
}
excelCell.SetCellValue("Statutory Minimum Wage Summary Report");
excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
workbook.Write(excelfileStream);
excelfileStream.Close();
WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
else
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Employee not selected");
}
}
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);
}
}
}
private void button13_Click(object sender, EventArgs e)
{
//清空报表
report1.ClearReport();
//设置报表为非只读
report1.ReadOnly = false;
//报表停止公式计算，这样速度快点
report1.CalcFormula(false);
//报表停止绘制
report1.AllowDraw(false);
string fileName = Application.StartupPath + "\\Test.xls";
NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook;
try
{
this.Cursor = Cursors.WaitCursor;
using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream);
NPOI.HSSF.UserModel.HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
if (sheet.LastRowNum == 0)
{
this.Cursor = Cursors.Arrow;
return;
}
else
{
//产生列
NPOI.HSSF.UserModel.HSSFRow row = sheet.GetRow(0);
//report1.AddColumn(row.Cells.Count);
report1.AddColumn(row.LastCellNum);
}
int rowIndex = 0;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
//增加行
rowIndex = report1.AddRow(Gscr.Band.Detail);
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
for (int i = 0; i < row.LastCellNum; i++)
{
Gscr.Cell cellRpt = report1[rowIndex, i];
NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(i);
if (cell == null)
{
cellRpt.Value = null;
}
else
{
switch (cell.CellType)
{
case NPOI.HSSF.UserModel.HSSFCellType.BLANK:
cellRpt.Value = null;
break;
case NPOI.HSSF.UserModel.HSSFCellType.BOOLEAN:
cellRpt.Value = cell.BooleanCellValue;
break;
case NPOI.HSSF.UserModel.HSSFCellType.NUMERIC:
cellRpt.Value = cell.ToString();
break;
case NPOI.HSSF.UserModel.HSSFCellType.STRING:
cellRpt.Value = cell.StringCellValue;
break;
case NPOI.HSSF.UserModel.HSSFCellType.ERROR:
cellRpt.Value = cell.ErrorCellValue;
break;
default:
break;
}
}
}
}
}
//报表恢复公式计算
report1.CalcFormula(true);
//报表恢复绘制
report1.AllowDraw(true);
this.Cursor = Cursors.Arrow;
MessageBox.Show("成功读取Excel文件【" + fileName + " 】。",
"提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception err)
{
report1.CalcFormula(true);
report1.AllowDraw(true);
this.Cursor = Cursors.Arrow;
MessageBox.Show("读取Excel文件失败。" + err.Message,
"提示", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="ds">要导出的数据源</param>
/// <param name="excelFilePath">文件的物理路径</param>
/// <param name="errorFun"></param>
/// <returns></returns>
public static bool Output(DataSet ds, string excelFilePath, Action <Exception> errorFun = null)
{
if (!System.IO.File.Exists(excelFilePath))
{
throw new Exception("Excel 文件不存在");
}
if (null == ds || ds.Tables.Count == 0)
{
return(false);
}
try
{
//1.0 创建工作薄 和 工作表对象
NPOI.HSSF.UserModel.HSSFWorkbook book;
using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite))
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile);
}
int sheetIndex = 0;
foreach (DataTable dt in ds.Tables)
{
string sheetName = dt.TableName ?? "sheet" + sheetIndex;
NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表
if (null == sheet1)
{
sheet1 = book.CreateSheet(sheetName);
}
else if (sheet1.SheetName != sheetName)
{
book.SetSheetName(sheetIndex, sheetName);
}
int beginRow = 1;
//2.0给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行
for (int i = 0; i < dt.Columns.Count; i++)
{
rowHead.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//3.0 填充表格数据
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + beginRow); //创建数据行
for (int j = 0; j < dt.Columns.Count; j++) //填充行数据
{
rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
sheetIndex++;
}
//4.0 写入文件
using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create))
{
book.Write(wfile);
}
return(true);
}
catch (Exception ex)
{
if (errorFun != null)
{
errorFun(ex);
}
return(false);
}
}
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);
}
