protected override void Execute(CodeActivityContext context)
{
var path = ReportPath.Get(context);
var dataTable = DataTable.Get(context);
var heading = Heading.Get(context);
GenerateHTMLReport report = new GenerateHTMLReport(path, dataTable, heading);
report.createHTMLReport();
}
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt;
var reportPath = new ReportPath();
var reportParameter = new ReportParameter();
var reportDocument = new ReportDocument();
int reportType = Request.QueryString["ReportType"].Toint();
DateTime fromDate = Request.QueryString["fromDate"].ToDate();
DateTime toDate = Request.QueryString["toDate"].ToDate();
DateTime aDate = Request.QueryString["aDate"].ToDate();
int reportOption = Request.QueryString["reportOption"].Toint();
int reportCategory = Request.QueryString["reportCategory"].Toint();
int vatRegID = Request.QueryString["VatRegID"].Toint();
int productID = Request.QueryString["productID"].Toint();
int warehouseID = Request.QueryString["warehouseID"].Toint();
var transactionID = Request.QueryString["transactionNo"];
string printOption = Request.QueryString["printOption"];
//******** Updated Datetime ********//
DateTime?fDate;
DateTime?tDate;
DateTime?adate;
int? vatRegiID;
int? warehouseid;
int? productid;
int? reportoption;
int? reportcategory;
string transactionid;
switch (reportType)
{
#region Supplier Report
case ReportType.SupplierReport:
var supplierProvider = new SuppliersProvider();
int supplierTypeID = Request.QueryString["SupplierTypeID"].ToInt();
dt = supplierProvider.GetSupplierInfoByTypeID(supplierTypeID);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.SupplierReportPath);
GetValue(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
break;
#endregion
}
}
public static Stimulsoft.Report.StiReport GetSti(Stimulsoft.Report.StiReport sti, EnPrintType peper)
{
try
{
ReportPath.CreatePath(ReportPath.ReportPath_);
ReportPath.CreatePath(ReportPath.ReportPath_ + @"\" + FolderName);
return(Taraz_One_A5(sti));
}
catch (Exception ex)
{
WebErrorLog.ErrorInstence.StartErrorLog(ex);
}
return(sti);
}
/// <summary>
/// Tests the data source.
/// </summary>
/// <param name="message">The message.</param>
/// <returns></returns>
public bool TestDataSource(out string message)
{
message = String.Empty;
string pathEnd = ReportPath.EndsWith("/") ? String.Empty : "/";
string dsPath = string.Concat(ReportPath, pathEnd, "DataSources/RockContext");
string itemType = String.Empty;
var client = GetClient(UserType.ContentManager);
client.GetItemType(null, dsPath, out itemType);
if (!itemType.Equals("datasource", StringComparison.InvariantCultureIgnoreCase))
{
message = "Rock Context Data Source not found.";
return(false);
}
return(true);
}
public static Stimulsoft.Report.StiReport GetSti(Stimulsoft.Report.StiReport sti, EnPrintType peper)
{
try
{
ReportPath.CreatePath(ReportPath.ReportPath_);
ReportPath.CreatePath(ReportPath.ReportPath_ + @"\" + FolderName);
switch (peper)
{
case EnPrintType.Pdf_A4: return(Order_List_A4(sti));
}
}
catch (Exception ex)
{
WebErrorLog.ErrorInstence.StartErrorLog(ex);
}
return(sti);
}
protected override DTSExecResult ExecuteCore(ITaskExecuteContext context)
{
// Concatenate Querystring
var reportPath = HttpUtility.UrlEncode(ReportPath.EnsureStartsWith("/"));
var builder = new StringBuilder(reportPath);
foreach (var arg in Arguments)
{
string strValue = arg.Value;
//Get the actual value
if (arg.ValueType == ReportArgumentValueType.Variable)
{
strValue = context.Variables.GetValue(arg.Value)?.ToString();
}
if (strValue == null)
{
builder.AppendFormat("&{0}:isnull=true", HttpUtility.UrlEncode(arg.Name));
}
else
{
builder.AppendFormat("&{0}={1}", HttpUtility.UrlEncode(arg.Name), HttpUtility.UrlEncode(strValue));
}
}
;
builder.AppendFormat("&rs:Command=Render&rs:Format={0}", ReportFormat);
//Get the connection
var cm = context.Connections.GetConnection(ServerConnection);
//Create a copy of the connection because we're going to change the URL
var conn = new HttpClientConnection(cm.AcquireConnection(context.Transaction)).Clone();
if (conn == null)
{
throw new Exception("Unable to acquire connection.");
}
// Configure Full Report Url
//Format: <serverUrl>?/<reportPath><parameters><options>
var uri = new UriBuilder(conn.ServerURL)
{
Query = builder.ToString()
};
conn.ServerURL = uri.Uri.ToString();
// Generate Report
context.Events.LogInformation(b => b.Message("Generating report: {0}", conn.ServerURL));
try
{
var data = conn.DownloadData();
if (!String.IsNullOrWhiteSpace(Content))
{
context.Variables.SetValue(Content, data);
}
context.Events.LogInformation(b => b.Message("Report generated with size {1}: {0}", conn.ServerURL, data?.Length));
} catch (Exception e)
{
//Handle some common exceptions
throw HandleCommonExceptions(e);
};
return(DTSExecResult.Success);
}
/// -----------------------------------------------------------------------------
/// <summary>
/// LoadSettings loads the settings from the Database and displays them
/// </summary>
/// -----------------------------------------------------------------------------
public override void LoadSettings()
{
try
{
if (Page.IsPostBack == false)
{
GetPortalRoles();
if (EnableDiscount != null)
{
if (EnableDiscount.ToString().Length > 0)
{
CbxEnableDiscount.Checked = Convert.ToBoolean(EnableDiscount.ToString());
}
}
if (ShowPriceOnCheckout != null)
{
if (ShowPriceOnCheckout.ToString().Length > 0)
{
CbxShowPriceOnCheckout.Checked = Convert.ToBoolean(ShowPriceOnCheckout.ToString());
}
}
if (DisplayRemains != null)
{
if (DisplayRemains.ToString().Length > 0)
{
CbxDisplayRemains.Checked = Convert.ToBoolean(DisplayRemains.ToString());
}
}
if (RoleName != null)
{
ddlRoles.SelectedValue = RoleName;
}
if (PageSize > 0)
{
ddlPageSize.SelectedValue = PageSize.ToString();
}
if (EmailMessage != null)
{
txtEmailMessage.Text = EmailMessage;
}
if (EmailFrom != null)
{
txtEmailFrom.Text = EmailFrom;
}
if (EmailNotify != null)
{
txtEmailNotify.Text = EmailNotify;
}
if (EmailBCC != null)
{
txtEmailBCC.Text = EmailBCC;
}
if (EmailSubject != null)
{
txtEmailSubject.Text = EmailSubject;
}
if (ReportServerURL != null)
{
txturlReportServer.Text = ReportServerURL.ToString();
}
if (ReportPath != null)
{
txtReportPath.Text = ReportPath.ToString();
}
if (ReportCredentialsUserName != null)
{
txtRSCredentialsUserName.Text = ReportCredentialsUserName.ToString();
}
if (ReportCredentialsPassword != null)
{
txtRSCredentialsPassword.Text = ReportCredentialsPassword.ToString();
}
if (ReportCredentialsDomain != null)
{
txtRSCredentialsDomain.Text = ReportCredentialsDomain.ToString();
}
if (jQueryUI != null)
{
txtjQueryUI.Text = jQueryUI.ToString();
}
if (ImagePath != null)
{
txtImagePath.Text = ImagePath.ToString();
}
if (WallImage != null)
{
txtWallImage.Text = WallImage.ToString();
}
}
}
catch (Exception exc) //Module failed to load
{
Exceptions.ProcessModuleLoadException(this, exc);
}
}
private void GetMsg(ReportParameter reportParameter, ReportDocument reportDocument, ReportPath reportPath)
{
reportDocument.Load(reportPath.MsgCReportPath);
GetAddressValue(reportParameter, reportDocument);
reportDocument.SetParameterValue("Notfind", reportParameter.NotFind);
technoCrystalReport.ReportSource = reportDocument;
technoCrystalReport.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt;
var reportPath = new ReportPath();
var reportParameter = new ReportParameter();
var reportDocument = new ReportDocument();
int reportType = Request.QueryString["ReportType"].Toint();
DateTime fromDate = Request.QueryString["fromDate"].ToDate();
DateTime toDate = Request.QueryString["toDate"].ToDate();
DateTime aDate = Request.QueryString["aDate"].ToDate();
int reportOption = Request.QueryString["reportOption"].Toint();
int reportCategory = Request.QueryString["reportCategory"].Toint();
int productID = Request.QueryString["productID"].Toint();
var transactionID = Request.QueryString["transactionNo"];
string printOption = Request.QueryString["printOption"];
//******** Updated Datetime ********//
DateTime?fDate;
DateTime?tDate;
DateTime?Date;
switch (reportType)
{
#region Supplier Report
case ReportType.SupplierReport: //////////// For Supplier Info report
var supplierProvider = new SupplierProvider();
int supplierTypeID = Request.QueryString["SupplierTypeID"].ToInt();
dt = supplierProvider.GetSupplierByTypeID(supplierTypeID);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.SupplierReportPath);
GetValue(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
break;
case ReportType.SupplierProductReport: //////////// For Supplier Product report
supplierProvider = new SupplierProvider();
int supplierID = Request.QueryString["SupplierID"].ToInt();
dt = supplierProvider.GetByID(supplierID);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.SupplierProductReportPath);
GetValue(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
break;
#endregion
#region Requisition Report
case ReportType.RequisitionReport: //////////// For Requisition report
var requisitionProvider = new RequisitionProvider();
fDate = GetNullfDatetime();
tDate = GetNulltDatetime();
Date = GetNullaDatetime();
if (reportOption == 3)
{
var requisitionNo = Request.QueryString["transactionNo"].Trim();
dt = requisitionProvider.GetByID(requisitionNo);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.RequisitionReportPath);
GetValueWithDate(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
else
{
dt = requisitionProvider.GetAllByDateWise(productID, transactionID, fDate, tDate, Date, reportOption);
if (dt != null && dt.Rows.Count > 0)
{
if (reportOption == 2)
{
reportDocument.Load(reportPath.RequisitionReportPath);
GetValueWithFromToDate(reportParameter, reportDocument, dt, fDate, tDate);
}
else if (reportOption == 1)
{
reportDocument.Load(reportPath.RequisitionReportPath);
GetValueAGetDate(dt, reportParameter, reportDocument, Date);
}
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
break;
#endregion
#region Purchase Order Report
case ReportType.PurchaseOrderReport: //////////// For Purchase Order report
var purchaseOrderProvider = new PurchaseOrderProvider();
fDate = GetNullfDatetime();
tDate = GetNulltDatetime();
if (reportCategory == 1)
{
var purchaseOrderNo = Request.QueryString["transactionNo"].Trim();
dt = purchaseOrderProvider.GetByID(purchaseOrderNo);
if (dt != null && dt.Rows.Count > 0)
{
decimal value = 0.00M;
decimal totalValue = 0.00M;
foreach (DataRow dr in dt.Rows)
{
value = Convert.ToDecimal(dr["Value"]);
totalValue = totalValue + value;
}
string textValue = NumberToText(Convert.ToInt32(totalValue), true);
System.Data.DataColumn newColumn = new System.Data.DataColumn("TotalAmountInText", typeof(System.String));
newColumn.DefaultValue = textValue;
dt.Columns.Add(newColumn);
reportDocument.Load(reportPath.PurchaseOrderReportPath);
GetValueWithDate(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
else if (reportCategory == 2) //// Purchase Order Detail Report - Single Product/////////////////////
{
dt = purchaseOrderProvider.GetAllData(productID);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.PurchaseOrderDetailReportPath);
GetValueWithFromToDate(reportParameter, reportDocument, dt, fDate, tDate);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
break;
#endregion
#region Delivery Challan Report
case ReportType.DeliveryChallanReport: //////////// For Delivery Challan Report
var deliveryChallanProvider = new DeliveryChallanProvider();
fDate = GetNullfDatetime();
tDate = GetNulltDatetime();
if (reportOption == 2)
{
var deliveryChallanNo = Request.QueryString["transactionNo"].Trim();
dt = deliveryChallanProvider.GetByID(deliveryChallanNo);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.DeliveryChallanReportPath);
GetValueWithDate(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
else
{
//dt = purchaseOrderProvider.GetAllByDateWise(productID, transactionID, fDate, tDate, adate, reportOption);
//if (dt != null && dt.Rows.Count > 0)
//{
// if (reportOption == 2)
// {
// reportDocument.Load(reportPath.RequisitionReportPath);
// GetValueWithFromToDate(reportParameter, reportDocument, dt, fDate, tDate);
// }
// else if (reportOption == 1)
// {
// reportDocument.Load(reportPath.RequisitionReportPath);
// GetValueAGetDate(dt, reportParameter, reportDocument, adate);
// }
//}
//else
//{
// GetMsg(reportParameter, reportDocument, reportPath);
//}
}
break;
#endregion
#region Production Requisition Report
case ReportType.ProductionRequisitionReport:
var productionRequisitionProvider = new ProductionRequisitionProvider();
fDate = GetNullfDatetime();
tDate = GetNulltDatetime();
if (reportOption == 2)
{
//var prodReqNo = Request.QueryString["transactionNo"].Trim();
//dt = productionRequisitionProvider.GetByID(prodReqNo);
//if (dt != null && dt.Rows.Count > 0)
//{
// reportDocument.Load(reportPath.ProductionRequisitionReportPath);
// GetValueWithDate(dt, reportParameter, reportDocument);
//}
//else
//{
// GetMsg(reportParameter, reportDocument, reportPath);
//}
}
else
{
}
break;
#endregion
#region Prodct Stock Report
case ReportType.ProductCurrentStockReport:
var productCurrentStockProvider = new ProductCurrentStockProvider
{
ProductID = Request.QueryString["ProductID"].ToInt(),
DivisionID = Request.QueryString["DivisionID"].ToInt(),
ProductType = Request.QueryString["ProductType"].ToInt(),
FromDate = Request.QueryString["fromDate"].ToString(),
Todate = Request.QueryString["todate"].ToString()
};
if (productCurrentStockProvider.ProductID == 0)
{
dt = productCurrentStockProvider.GetDivisionAndItemwise();
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.AllProductStockReportPath);
GetValueWithDate(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
else
{
var mushak16ProviderList = productCurrentStockProvider.GetAll();
if (mushak16ProviderList != null)
{
reportDocument.Load(reportPath.ProductIndividualStockReportPath);
reportDocument.SetDataSource(mushak16ProviderList);
GetAddressValue(reportParameter, reportDocument);
technoCrystalReport.ReportSource = reportDocument;
technoCrystalReport.DataBind();
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
break;
#endregion
#region LC Report
case ReportType.LCReport: //////////// For LC report
var lCProvider = new LCProvider();
fDate = GetNullfDatetime();
tDate = GetNulltDatetime();
if (reportCategory == 2) /////////////// LC Detail Report ///////////////
{
dt = lCProvider.GetByDateRangeWise(fromDate, toDate, reportCategory);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.LCDetailReportPath);
GetValueWithDate(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
else if (reportCategory == 1) //// LC Summary Report /////////////////////
{
dt = lCProvider.GetByDateRangeWise(fromDate, toDate, reportCategory);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.LCSummaryReportPath);
GetValueWithFromToDate(reportParameter, reportDocument, dt, fDate, tDate);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
break;
#endregion
#region BOE Report
case ReportType.BOEReport: //////////// For BOE report
var bOEProvider = new BOEProvider();
fDate = GetNullfDatetime();
tDate = GetNulltDatetime();
if (reportCategory == 2)
{
dt = bOEProvider.GetByDateRangeWise(fromDate, toDate, reportCategory);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.BOEDetailReportPath);
GetValueWithDate(dt, reportParameter, reportDocument);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
else if (reportCategory == 1) //// BOE Summary Report /////////////////////
{
dt = bOEProvider.GetByDateRangeWise(fromDate, toDate, reportCategory);
if (dt != null && dt.Rows.Count > 0)
{
reportDocument.Load(reportPath.BOESummaryReportPath);
GetValueWithFromToDate(reportParameter, reportDocument, dt, fDate, tDate);
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
}
break;
#endregion
#region QA Report
case ReportType.QAReport: //////////// For QA report
var qAQCRequisitionProvider = new QAQCRequisitionProvider();
fDate = GetNullfDatetime();
tDate = GetNulltDatetime();
Date = GetNullaDatetime();
dt = qAQCRequisitionProvider.GetDateWiseProductInfo(fDate, tDate, Date, productID);
if (dt != null && dt.Rows.Count > 0)
{
{
//if (reportOption == 1 && adate == null) //All product summary report
//{
// reportDocument.Load(reportPath.QAReportPath);
// GetValueWithDate(dt, reportParameter, reportDocument);
//}
//if (reportOption == 1 && adate == null) //All product summary report
//{
// reportDocument.Load(reportPath.QAReportPath);
// GetValueWithDate(dt, reportParameter, reportDocument);
//}
if (reportOption == 1)
{
reportDocument.Load(reportPath.QAReportPath);
GetValueAGetDate(dt, reportParameter, reportDocument, Date);
}
else if (reportOption == 2)
{
reportDocument.Load(reportPath.QAReportPath);
GetValueWithFromToDate(reportParameter, reportDocument, dt, fDate, tDate);
}
//else if (reportOption == 3) // Purchase ID wise report (showing product details).
//{
// reportDocument.Load(reportPath.QAReportPath);
// GetValueWithDate(dt, reportParameter, reportDocument, printOption);
//}
}
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
break;
#endregion
#region Eng. Internal Requisition Report
case ReportType.EngineeringInternalRequiReport: //////////// For Eng Requi report
var engRequisitionProvider = new EngineeringRequisitionProvider();
var engRequisitionNo = Request.QueryString["transactionNo"].Trim();
dt = engRequisitionProvider.GetByID(engRequisitionNo);
if (dt != null && dt.Rows.Count > 0)
{
{
reportDocument.Load(reportPath.EngInternalRequiReportPath);
GetValueWithDate(dt, reportParameter, reportDocument);
}
}
else
{
GetMsg(reportParameter, reportDocument, reportPath);
}
break;
#endregion
}
}
private void Importbutton_Click(object sender, EventArgs e)
{
Openfile = new OpenFileDialog()
{
Filter = "*.Xlsx| *.xlsx"
};
if (Openfile.ShowDialog() == DialogResult.OK)
{
try
{
ReportPath = Openfile.FileName;
if (ReportPath != null)
{
using (FileStream file = new FileStream($"{ReportPath}", FileMode.Open, FileAccess.Read))
{
xworkbook = new XSSFWorkbook(file); //Ecexl檔案載入
int sheet = xworkbook.NumberOfSheets; //取得分頁數量
for (int Sheetnum = 1; Sheetnum < 2; Sheetnum++)
{
var data = xworkbook.GetSheetAt(Sheetnum); //載入分頁資訊
for (int Rownum = 9; Rownum < data.LastRowNum; Rownum++) //每一行資料
{
IRow row = data.GetRow(Rownum);
if (row.GetCell(0).ToString() == "以下空白")
{
cell1.Add(row.GetCell(0));
break;
}
else
{
cell1.Add(row.GetCell(0));
}
if (row.GetCell(1).ToString() == "以下空白")
{
cell2.Add(row.GetCell(1));
break;
}
else
{
cell2.Add(row.GetCell(1));
}
if (row.GetCell(2).ToString() == "以下空白")
{
cell3.Add(row.GetCell(2));
break;
}
else
{
cell3.Add(row.GetCell(2));
}
if (row.GetCell(3).ToString() == "以下空白")
{
cell4.Add(row.GetCell(3));
break;
}
else
{
cell4.Add(row.GetCell(3));
}
if (row.GetCell(4).ToString() == "以下空白")
{
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
break;
}
else
{
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
}
if (row.GetCell(5).ToString() == "以下空白")
{
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
break;
}
else
{
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
}
if (row.GetCell(6).ToString() == "以下空白")
{
cell7.Add(row.GetCell(6));
break;
}
else
{
cell7.Add(row.GetCell(6));
}
if (row.GetCell(7).ToString() == "以下空白")
{
cell8.Add(row.GetCell(7));
break;
}
else
{
cell8.Add(row.GetCell(7));
}
}
}
}
}
}
catch (DirectoryNotFoundException ex) { Log.Error(ex, $" [{ReportPath}] KWH查無此資料夾路徑"); }
catch (FileNotFoundException ex) { Log.Error(ex, $"KWH查無此資料檔案"); }
catch (Exception ex) { Log.Error(ex, $"KWH資料匯入失敗 檔案名稱{FieldName}"); }
}
filenamelabel.Text = ReportPath.Split('.')[0].Split('\\')[ReportPath.Split('.')[0].Split('\\').Length - 1];
dataGridView1.ColumnCount = 8;
dataGridView1.Columns[0].Name = Convert.ToString(cell1[0]);
dataGridView1.Columns[1].Name = Convert.ToString(cell2[0]);
dataGridView1.Columns[2].Name = Convert.ToString(cell3[0]);
dataGridView1.Columns[3].Name = Convert.ToString(cell4[0]);
dataGridView1.Columns[4].Name = Convert.ToString(cell5[0]);
dataGridView1.Columns[5].Name = Convert.ToString(cell6[0]);
dataGridView1.Columns[6].Name = Convert.ToString(cell7[0]);
dataGridView1.Columns[7].Name = Convert.ToString(cell8[0]);
for (int i = 1; i < cell1.Count; i++)
{
dataGridView1.Rows.Add(cell1[i], cell2[i], cell3[i], cell4[i], cell5[i], cell6[i], cell7[i], cell8[i]);
}
}
private void OpenFilesimpleButton_Click(object sender, EventArgs e)
{
InsertSQLsimpleButton.Enabled = true;
if (gridControl1.DataSource != null)
{
gridView1.Columns.Clear();
}
#region excel資料匯入
Openfile = new OpenFileDialog()
{
Filter = "*.Xlsx| *.xlsx"
};
if (Openfile.ShowDialog() == DialogResult.OK)
{
try
{
ReportPath = Openfile.FileName;
if (ReportPath != null)
{
using (FileStream file = new FileStream($"{ReportPath}", FileMode.Open, FileAccess.Read))
{
xworkbook = new XSSFWorkbook(file); //Ecexl檔案載入
int sheet = xworkbook.NumberOfSheets; //取得分頁數量
for (int Sheetnum = 1; Sheetnum < 2; Sheetnum++)
{
var data = xworkbook.GetSheetAt(Sheetnum); //載入分頁資訊
for (int Rownum = 9; Rownum < data.LastRowNum; Rownum++) //每一行資料
{
IRow row = data.GetRow(Rownum);
#region 資料抓取
if (row.GetCell(0).ToString() == "" & row.GetCell(1).ToString() == "" & row.GetCell(2).ToString() == "" & row.GetCell(3).ToString() == "" & row.GetCell(4).ToString() == "" & row.GetCell(5).ToString() == "" & row.GetCell(6).ToString() == "" & row.GetCell(7).ToString() == "")
{
}
else
{
if (row.GetCell(7).ToString() == "以下空白")
{
cell1.Add(row.GetCell(0));
cell2.Add(row.GetCell(1));
cell3.Add(row.GetCell(2));
cell4.Add(row.GetCell(3));
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
cell7.Add(row.GetCell(6));
cell8.Add(row.GetCell(7));
break;
}
else
{
cell1.Add(row.GetCell(0));
cell2.Add(row.GetCell(1));
cell3.Add(row.GetCell(2));
cell4.Add(row.GetCell(3));
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
cell7.Add(row.GetCell(6));
cell8.Add(row.GetCell(7));
}
}
#endregion
}
}
}
}
}
catch (DirectoryNotFoundException ex) { Log.Error(ex, $" [{ReportPath}] 查無此資料夾路徑"); }
catch (FileNotFoundException ex) { Log.Error(ex, $"查無此資料檔案"); }
catch (Exception ex) { Log.Error(ex, $"資料匯入失敗 檔案名稱{FieldName}"); }
}
#endregion
#region GridView顯示匯入excel資料
labelControl2.Text = ReportPath.Split('.')[0].Split('\\')[ReportPath.Split('.')[0].Split('\\').Length - 1];
DataTable dataTable = new DataTable();
dataTable.Columns.Add(Convert.ToString(cell1[0]));
dataTable.Columns.Add(Convert.ToString(cell2[0]));
dataTable.Columns.Add(Convert.ToString(cell3[0]));
dataTable.Columns.Add(Convert.ToString(cell4[0]));
dataTable.Columns.Add(Convert.ToString(cell5[0]));
dataTable.Columns.Add(Convert.ToString(cell6[0]));
dataTable.Columns.Add(Convert.ToString(cell7[0]));
dataTable.Columns.Add(Convert.ToString(cell8[0]));
for (int i = 1; i < cell1.Count; i++)
{
dataTable.Rows.Add(cell1[i], cell2[i], cell3[i], cell4[i], cell5[i], cell6[i], cell7[i], cell8[i]);
}
gridControl1.DataSource = dataTable;
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.Columns[0].BestFit();
gridView1.Columns[1].BestFit();
gridView1.Columns[2].BestFit();
gridView1.Columns[3].BestFit();
gridView1.Columns[4].BestFit();
gridView1.Columns[5].BestFit();
gridView1.Columns[6].BestFit();
gridView1.Columns[7].BestFit();
for (int i = 0; i < gridView1.Columns.Count; i++)
{
gridView1.Columns[i].OptionsColumn.AllowEdit = false;
}
#endregion
}
