/// <summary>
///
/// </summary>
public static void ReadExcel()
{
using FileStream fs = new FileStream(@"C:\Read.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
using ExcelPackage excel = new ExcelPackage(fs);
ExcelWorksheet sheet = excel.Workbook.Worksheets[1]; //取得Sheet1
int startRowNumber = sheet.Dimension.Start.Row; //起始列編號，從1算起
int endRowNumber = sheet.Dimension.End.Row; //結束列編號，從1算起
int startColumn = sheet.Dimension.Start.Column; //開始欄編號，從1算起
int endColumn = sheet.Dimension.End.Column; //結束欄編號，從1算起
bool isHeader = true; //有包含標題
if (isHeader)
{
startRowNumber += 1;
}
for (int currentRow = startRowNumber; currentRow <= endRowNumber; currentRow++)
{
ExcelRange range = sheet.Cells[currentRow, startColumn, currentRow, endColumn]; //抓出目前的Excel列
if (!range.Any(c => !string.IsNullOrEmpty(c.Text))) //這是一個完全空白列(使用者用Delete鍵刪除動作)
{
continue; //略過此列
}
//讀值
string cellValue = sheet.Cells[currentRow, 1].Text;//讀取格式化過後的文字(讀取使用者看到的文字)
}
}
public virtual void ImportFromExcel(string filePath, bool withHeader = true)
{
GameDataTableBase <T> table = target as GameDataTableBase <T>;
FileStream fstream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
ExcelPackage ep = new ExcelPackage(fstream);
ExcelWorksheet sheet = ep.Workbook.Worksheets[sheetName];
T dataSample = new T();
List <string> fieldNames = dataSample.GetFieldNames();
int i = (withHeader) ? 1 : 0;
int dataIndex = 0;
while (true)
{
ExcelRange range = sheet.GetExcelRange(i, 0, i, fieldNames.Count - 1);
if (range.Any(c => !string.IsNullOrEmpty(c.Text)) == false) //all empty row
{
break;
}
if (table.DataList.Count <= dataIndex)
{
table.DataList.Add(new T());
}
for (int j = 0; j < fieldNames.Count; j++)
{
string text = sheet.GetValueInCell(i, j);
table.DataList[dataIndex].SetField(typeof(T).GetField(fieldNames[j]), text);
}
i++;
dataIndex++;
}
fstream.Close();
ep.Dispose();
Debug.Log("Import from Excel Process Completed. Source: " + filePath);
}
/// <summary>
/// Parses a row of data or returns null if the row should be skipped
/// </summary>
protected virtual object OnReadRow(ExcelRange range, ISheetModel model, IColumnModel[] columnMapping)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (model == null)
{
throw new ArgumentNullException(nameof(range));
}
if (columnMapping == null)
{
throw new ArgumentNullException(nameof(columnMapping));
}
var firstCol = range.Start.Column;
var row = range.Start.Row;
var columns = range.Columns;
if (range.Rows != 1)
{
throw new ArgumentOutOfRangeException(nameof(range), "Range must represent a single row of data");
}
if (columns != columnMapping.Length)
{
throw new ArgumentOutOfRangeException(nameof(columnMapping), "Number of columns in range does not match size of columnMapping array");
}
var obj = Activator.CreateInstance(model.Type);
if (range.Any(x => x.Value != null))
{
for (int colIndex = 0; colIndex < columns; colIndex++)
{
var col = colIndex + firstCol;
var columnModel = columnMapping[colIndex];
if (columnModel != null)
{
var cell = range[row, col]; // note that range[] resets range.Address to equal the new address
if (cell.Value == null)
{
if (!columnModel.Optional)
{
throw new ColumnDataMissingException(columnModel.Name, model.Name);
}
}
else
{
object value;
try {
if (columnModel.ReadSerializer != null)
{
value = columnModel.ReadSerializer(cell);
}
else
{
value = DefaultReadSerializer(cell, columnModel.Type);
}
}
catch (Exception e) {
throw new ParseDataException(cell.Address, columnModel.Name, model.Name, e);
}
if (value != null)
{
if (columnModel.Member is PropertyInfo propertyInfo)
{
propertyInfo.SetMethod.Invoke(obj, new[] { value });
}
else if (columnModel.Member is FieldInfo fieldInfo)
{
fieldInfo.SetValue(obj, value);
}
}
}
}
}
}
else
{
if (model.SkipEmptyRows)
{
obj = null;
}
else
{
foreach (var columnModel in columnMapping)
{
if (!columnModel.Optional)
{
throw new RowEmptyException(model.Name);
}
}
}
}
return(obj);
}
public static DataTable ToDataTable(this ExcelPackage package)
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
DataTable Dt = new DataTable();
//抓成交年月
//1.抓標題 2.抓銀行 --->到合計停止 so 思考for抓的範圍 要抓哪幾筆 (有值後 往右延伸N筆
//散值該放哪裡? 可以放第二個table嗎
//*****處理好標題遺漏的問題******
workSheet.Cells[9, 15].Value = "占公司成交比率(筆數)";
workSheet.Cells[9, 16].Value = "占公司成交比率(金額)";
workSheet.Cells[9, 17].Value = "占市場成交比率(筆數)";
workSheet.Cells[9, 18].Value = "占市場成交比率(金額)";
foreach (var firstRowCell in workSheet.Cells[9, 1, 1, workSheet.Dimension.End.Column])
{
Dt.Columns.Add(firstRowCell.Text);
}
// 從11開始 一直向下探
// workSheet.Dimension.End.Row -2 把最後兩行備註忽略不看
for (var rowNumber = 11; rowNumber <= workSheet.Dimension.End.Row - 2; rowNumber++)
{
//這邊應該要修改為 向下一直判斷 有null跳過 沒有null向右延伸19次
//抓出目前的Excel列
ExcelRange range = workSheet.Cells[rowNumber, 1];
if (range.Any(c => !string.IsNullOrEmpty(c.Text)) == true)
//這是一個完全空白列(使用者用Delete鍵刪除動作)
//空白有兩種 一種是使用者delete的空白，另一種是起初就沒有值
{
//從rowNumber = 11 開始 一直向右探
var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
var newRow = Dt.NewRow();
foreach (var cell in row)
{
if (cell.Start.Column == 1)
{
newRow[cell.Start.Column - 1] = cell.Style.Numberformat.Format = "0";
}
else if (cell.Start.Column == 4 || cell.Start.Column == 5 || cell.Start.Column == 6 || cell.Start.Column == 8 || cell.Start.Column == 9 || cell.Start.Column == 10 || cell.Start.Column == 11 || cell.Start.Column == 12 || cell.Start.Column == 13 || cell.Start.Column == 14)
{
newRow[cell.Start.Column - 1] = cell.Style.Numberformat.Format = "0";
}
else if (cell.Start.Column == 15 || cell.Start.Column == 16 || cell.Start.Column == 17 || cell.Start.Column == 18 || cell.Start.Column == 19)
{
newRow[cell.Start.Column - 1] = cell.Style.Numberformat.Format = "0.00%";
}
newRow[cell.Start.Column - 1] = cell.Text;
//newRow[cell.Start.Column - 1] = cell.RichText != null ? cell.RichText.Text : cell.Text;
}
Dt.Rows.Add(newRow);
}
}
{
//using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString))
//SqlConnection cn = new SqlConnection();
//cn.ConnectionString = "Data Source=DESKTOP-3ITCRT5;Initial Catalog=eleTransportDetal;User ID=jobuser; Password=1q2w3e4r5t_";
using (var cn = new SqlConnection(WebConfigurationManager.ConnectionStrings["AseCRSConnectionString"].ConnectionString.ToString()))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand(@"if exists (select * from eleTransportDetal..tbleleTransportDetal)
begin delete from eleTransportDetal..tbleleTransportDetal where 證券商代號='1001' or 證券商代號='1002' or 證券商代號='1010' or 證券商代號='1125' or 證券商代號='合計'
end;" , cn))
using (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add("證券商代號", "證券商代號");
copy.ColumnMappings.Add("名稱", "名稱");
copy.ColumnMappings.Add("開辦日期", "開辦日期");
copy.ColumnMappings.Add("累計開戶數", "累計開戶數");
copy.ColumnMappings.Add("本月交易戶數", "本月交易戶數");
copy.ColumnMappings.Add("本月交易人數", "本月交易人數");
copy.ColumnMappings.Add("平均每戶成交金額", "平均每戶成交金額");
copy.ColumnMappings.Add("委託筆數", "委託筆數");
copy.ColumnMappings.Add("委託金額", "委託金額");
copy.ColumnMappings.Add("成交筆數", "成交筆數");
copy.ColumnMappings.Add("成交金額", "成交金額");
copy.ColumnMappings.Add("平均每筆成交金額", "平均每筆成交金額");
copy.ColumnMappings.Add("公司總成交筆數", "公司總成交筆數");
copy.ColumnMappings.Add("公司總成交金額", "公司總成交金額");
copy.ColumnMappings.Add("占公司成交比率(筆數)", "占公司成交比率(筆數)");
copy.ColumnMappings.Add("占公司成交比率(金額)", "占公司成交比率(金額)");
copy.ColumnMappings.Add("占市場成交比率(筆數)", "占市場成交比率(筆數)");
copy.ColumnMappings.Add("占市場成交比率(金額)", "占市場成交比率(金額)");
copy.ColumnMappings.Add("市場電子式成交額比率", "市場電子式成交額比率");
copy.DestinationTableName = "tbleleTransportDetal";
cmd.ExecuteNonQuery();
copy.WriteToServer(Dt);
cn.Close();
}
}
}
return(Dt);
}
public string ReadMembersExcelFile(string uploadedFileName, string basePathDirectory, string fileName, string userIdentity, FileHandleDetails fileHandleDetails)
{
var keyRowValueList = new List <string>();
var membersTableRowList = new List <Member>();
var membersFileErrorToBulk = new List <CoecoErrorMemberBulk>();
var unitsID = _dataAccessService.GetAllUnits().Select(x => x.ID).ToList();
var permissionsProfilesID = _dataAccessService.GetAllPermissionsProfile().Select(x => x.ID).ToList();
using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(fileName)))
{
ExcelWorksheet myWorksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here
int totalRows = myWorksheet.Dimension.End.Row;
int totalColumns = myWorksheet.Dimension.End.Column;
int startRow = myWorksheet.Dimension.Start.Row;
List <KeyValuePair <string, string> > errorMeshartimTableRowList = new List <KeyValuePair <string, string> >();
for (int rowNum = startRow; rowNum <= totalRows; rowNum++) //selet starting row here
{
ExcelRange range = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns];
if (range.Any(c => c.Value != null))
{
try
{
var errMessage = string.Empty;
var TZ = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.TZ].Text;
var firstName = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.FirstName].Text;
var lastName = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.LastName].Text;
var email = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.Email].Text;
var phoneNumber = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.PhoneNumber].Text;
var role = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.Role].Text;
var unitID = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.UnitID].Text;
var permissionsProfileID = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.PermissionsProfileID].Text;
errMessage = _fileValidation.CheckTZFromExecl(TZ);
if (errMessage == string.Empty)
{
errMessage = _fileValidation.CheckUnitOrProfilePermmisionIsExists(unitID, unitsID, TableName.Unit.ToString());
}
if (errMessage == string.Empty)
{
errMessage = _fileValidation.CheckUnitOrProfilePermmisionIsExists(permissionsProfileID, permissionsProfilesID, TableName.PermissionsProfile.ToString());
}
DateTime now = DateTime.Now;
if (errMessage == string.Empty)
{
keyRowValueList.Add(TZ);
Member memberTableRow = new Member
{
TZ = TZ,
FirstName = firstName,
LastName = lastName,
Email = email,
PhoneNumber = phoneNumber,
Role = role,
UnitID = int.Parse(unitID),
PermissionsProfileID = int.Parse(permissionsProfileID)
};
membersTableRowList.Add(memberTableRow);
}
else
{
membersFileErrorToBulk.Add(new CoecoErrorMemberBulk()
{
CreatedBy = userIdentity,
CreatedOn = now,
Disable = false,
UpdatedBy = userIdentity,
UpdatedOn = now,
ErrorMsg = errMessage,
FilesUploadName = fileName,
TZ = TZ,
});
}
}
catch (Exception ex)
{
_loggerService.InsertLogMessage(LogType.Error.ToString(), $"Failed on ReadMembersExcelFile {ex.ToString()}", userIdentity);
}
}
}
}
SetDefaultValues(membersTableRowList, userIdentity);
if (CheckDuplicated(keyRowValueList) == false)
{
var membersToInsertBulk = membersTableRowList.ToList().Select(d => new CoecoMemberBulk
{
ID = d.ID,
TZ = d.TZ,
Email = d.Email,
Role = d.Role,
PermissionsProfileID = d.PermissionsProfileID,
UnitID = d.UnitID,
PhoneNumber = d.PhoneNumber,
CreatedOn = d.CreatedOn,
CreatedBy = d.CreatedBy,
UpdatedOn = d.UpdatedOn,
UpdatedBy = d.UpdatedBy,
Disable = d.Disable,
FirstName = d.FirstName,
LastName = d.LastName,
}).ToList();
bool isMeshartimUploadSuceed = true;
if (membersToInsertBulk.Any())
{
isMeshartimUploadSuceed = BulkInsert.Copy <CoecoMemberBulk>(membersToInsertBulk, "dbo.SYS_TBL_Members", "#Members", "Members added: ");
}
if (membersFileErrorToBulk.Any())
{
BulkInsert.Copy <CoecoErrorMemberBulk>(membersFileErrorToBulk, "dbo.SYS_TBL_MembersFileError", "#MembersFileError", "MembersFileError added: ");
}
string messageString = FileReturnMsg.FileWasHandle.ToString();
if (!(isMeshartimUploadSuceed))
{
messageString = FileReturnMsg.MainUploadFileFailed.ToString();
}
return(messageString);
}
else
{
return(FileReturnMsg.DuplicatedRowsProblem.ToString());
}
}
public JsonResult UploadFile()
{
try
{
string path = Server.MapPath("~/Uploads/");
_list = new List <ExcelModel>();
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
HttpPostedFileBase file = Request.Files["inputFile[]"] as HttpPostedFileBase;
string filePath = path + Path.GetFileName(file.FileName);
file.SaveAs(filePath);
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
//載入Excel檔案
using (ExcelPackage ep = new ExcelPackage(fs))
{
ExcelWorksheet sheet = ep.Workbook.Worksheets[1];
int startRowNumber = sheet.Dimension.Start.Row;
int endRowNumber = sheet.Dimension.End.Row;
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
bool isHeader = true;
if (isHeader)
{
startRowNumber += 1;
}
for (int currentRow = startRowNumber; currentRow <= endRowNumber; currentRow++)
{
//for (int currentColumn = startColumn; currentColumn <= endColumn; currentColumn++)
//{
//}
ExcelRange range = sheet.Cells[currentRow, startColumn, currentRow, endColumn];//抓出目前的Excel列
if (range.Any(c => !string.IsNullOrEmpty(c.Text)) == false)
{
continue;//略過此列
}
//讀值
_list.Add(new ExcelModel
{
Email = sheet.Cells[currentRow, 1].Text,
Name = sheet.Cells[currentRow, 2].Text,
C = sheet.Cells[currentRow, 3].Text,
M = int.Parse(sheet.Cells[currentRow, 4].Text, NumberStyles.Number, CultureInfo.InvariantCulture),
Box = int.Parse(sheet.Cells[currentRow, 5].Text, NumberStyles.Number, CultureInfo.InvariantCulture),
B = int.Parse(sheet.Cells[currentRow, 6].Text, NumberStyles.Number, CultureInfo.InvariantCulture),
In = int.Parse(sheet.Cells[currentRow, 7].Text, NumberStyles.Number, CultureInfo.InvariantCulture),
N = int.Parse(sheet.Cells[currentRow, 8].Text, NumberStyles.Number, CultureInfo.InvariantCulture),
All = int.Parse(sheet.Cells[currentRow, 9].Text, NumberStyles.Number, CultureInfo.InvariantCulture),
IsSend = false.ToString().ToLower()
});
}
}
}
}
catch (Exception e)
{
return(Json(new { status = false, message = e.ToString() }, JsonRequestBehavior.AllowGet));
}
string result = JsonConvert.SerializeObject(_list);
return(Json(new { status = true, message = result }, JsonRequestBehavior.AllowGet));
}