/* public static DataTable ConvertCSVtoDataTable(string strFilePath) { StreamReader sr = new StreamReader(strFilePath); string[] headers = sr.ReadLine().Split(','); DataTable dt = new DataTable(); foreach (string header in headers) { dt.Columns.Add(header); } while (!sr.EndOfStream) { string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)"); DataRow dr = dt.NewRow(); for (int i = 0; i < headers.Length; i++) { dr[i] = rows[i]; } dt.Rows.Add(dr); } return dt; } */ public static void createXLS(DataTable dt) { FileStream stream = new FileStream("YAPILANDIRMALAR.xls", FileMode.OpenOrCreate); ExcelWriter writer = new ExcelWriter(stream); writer.BeginWrite(); for (int i = 0; i < dt.Columns.Count; i++) { string name = dt.Columns[i].ColumnName.ToString(); writer.WriteCell(0, i, name); } /* */ for (int r = 0; r < dt.Rows.Count; r++) { //string TempStr = HeadStr; //Sb.Append("{"); for (int c = 0; c < dt.Columns.Count; c++) { writer.WriteCell(r+1, c, dt.Rows[r][c].ToString()); // TempStr = TempStr.Replace("<br>", Environment.NewLine).Replace(Dt.Columns[j] + j.ToString() + "¾", Dt.Rows[r][c].ToString()); } //Sb.Append(TempStr + "},"); } writer.EndWrite(); stream.Close(); }
public void Write(IEnumerable <Dictionary <string, object> > datas, Dictionary <string, object> fields, string path) { FileStream stream = new FileStream(path, System.IO.FileMode.OpenOrCreate); ExcelWriter xlwriter = new ExcelWriter(stream); xlwriter.BeginWrite(); foreach (var field in fields.Select((value, index) => new { index = index, value = value })) { var obj = field.value.Key; xlwriter.WriteCell(0, field.index, (field.value.Value).ToString()); foreach (var data in datas.Select((value, index) => new { value = value, index = index + 1 })) { if (data.value.ContainsKey(obj)) { if (data.value[obj] != null) { xlwriter.WriteCell(data.index, field.index, data.value[obj].ToString()); } } } } xlwriter.EndWrite(); stream.Close(); }
public static void ToExcel(string excelSavePath, DataTable sourceTable) { int cols = sourceTable.Columns.Count; int rows = sourceTable.Rows.Count; ExcelWriter excel = new ExcelWriter(excelSavePath); excel.BeginWrite(); //标题栏 for (int c = 0; c < cols; c++) { excel.WriteString((short)0, (short)c, sourceTable.Columns[c].ToString()); } //数据 string productNumber = null; int rowIndex = 0; for (int r = 0; r < rows; r++) { if (productNumber != sourceTable.Rows[r][0].ToString()) { productNumber = sourceTable.Rows[r][0].ToString(); excel.WriteString((short)(r + 1), (short)0, productNumber); } for (int c = 1; c < cols; c++) { excel.WriteString((short)(r + 1), (short)c, sourceTable.Rows[r][c].ToString()); } } excel.EndWrite(); }
public void Write <T>(IEnumerable <T> datas, Dictionary <string, object> fields, string path) { FileStream stream = new FileStream(path, System.IO.FileMode.OpenOrCreate); ExcelWriter xlwriter = new ExcelWriter(stream); xlwriter.BeginWrite(); if (fields == null) { fields = new Dictionary <string, object>(); foreach (var item in datas.FirstOrDefault().GetType().GetProperties()) { fields.Add(item.Name, item.Name); } } foreach (var field in fields.Select((value, index) => new { index = index, value = value })) { var obj = field.value.Key; xlwriter.WriteCell(0, field.index, field.value.Value.ToString()); foreach (var data in datas.Select((value, index) => new { value = value, index = index + 1 })) { if (typeof(T).IsClass) { var key = data.value.GetType().GetProperty(field.value.Key).GetValue(data.value, null); if (key != null) { var value = key.ToString(); xlwriter.WriteCell(data.index, field.index, value); } } } } xlwriter.EndWrite(); stream.Close(); }
private PaymentTransaction paymentViaBlock(byte[] file) { //gach no theo lo Company currentCom = ((EInvoiceContext)FXContext.Current).CurrentCompany; PaymentTransaction mTran = new PaymentTransaction(); mTran.Data = file; mTran.ComID = currentCom.id; mTran.id = Guid.NewGuid(); mTran.AccountName = HttpContext.User.Identity.Name; mTran.Status = PaymentTransactionStatus.NewUpload; string message = ""; IPaymentExcel paymentExcelSrv = IoC.Resolve <IPaymentExcel>(); List <string> lstFkey = paymentExcelSrv.GetFkey(file, mTran.id.ToString(), out message); if (lstFkey == null) { mTran.Messages = message; mTran.Status = PaymentTransactionStatus.Failed; return(mTran); } List <IInvoice> invLst = new List <IInvoice>(); PublishInvoice pub = DefaultPublishInvoice(currentCom.id); IInvoiceService invSrv = InvServiceFactory.GetService(pub.InvPattern, currentCom.id); int count = lstFkey.Count(); log.Info("paymentViaBlock get Inv"); List <IInvoice> foundLst = new List <IInvoice>(); foundLst = (List <IInvoice>)invSrv.GetByFkey(currentCom.id, lstFkey.ToArray()); log.Info("paymentViaBlock loc ket qua"); List <string> foundFkey = foundLst.Select(inv => inv.Fkey).ToList(); //khong ton tai lstFkey.RemoveAll(inv => foundFkey.Contains(inv)); //đã gạch nợ rồi List <IInvoice> updateInv = foundLst.GetRange(0, foundLst.Count); updateInv.RemoveAll(inv => inv.PaymentStatus == Payment.Paid); //cần gạch nợ foundLst.RemoveAll(inv => inv.PaymentStatus == Payment.Unpaid); //đã gạch nợ rồi. List <string> unpaiedLst = updateInv.Select(inv => inv.Fkey).ToList(); List <string> paiedList = foundLst.Select(inv => inv.Fkey).ToList(); //ko ton tai lstFkey //da gach no paiedList //can gach no unpaiedLst //ghi file excel ket qua MemoryStream stream; ExcelWriter writer; if (lstFkey.Count > 0 || paiedList.Count > 0) { stream = new MemoryStream(); writer = new ExcelWriter(stream); writer.BeginWrite(); int i = 0; for (; i < lstFkey.Count; i++) { string s = lstFkey.ElementAt(i); writer.WriteCell(i, 0, s); writer.WriteCell(i, 1, "Khong ton tai"); } for (int j = 0; j < paiedList.Count; j++) { string s = paiedList.ElementAt(j); i++; writer.WriteCell(i, 0, s); writer.WriteCell(i, 1, "Da thanh toan tu truoc"); } writer.EndWrite(); mTran.FailResult = stream.ToArray(); stream.Close(); } //thuc hien gach no va ghi note moi int[] ids = updateInv.Select(inv => inv.id).ToArray(); string noteAppends = " || Thực hiện gạch nợ: Người gạch nợ: " + HttpContext.User.Identity.Name + " Ngày gạch nợ: " + DateTime.Now.ToString(); log.Info("paymentViaBlock gach no"); if (invSrv.ConfirmPayment(ids, noteAppends)) //if (invSrv.ConfirmPayment(updateInv)) { //thanh cong het //tao file ket qua thanh cong log.Info("paymentViaBlock ghi file thanh cong"); stream = new MemoryStream(); writer = new ExcelWriter(stream); writer.BeginWrite(); for (int j = 0; j < updateInv.Count; j++) { string s = updateInv.ElementAt(j).Fkey; writer.WriteCell(j, 0, s); } writer.EndWrite(); mTran.CompleteResult = stream.ToArray(); stream.Close(); if (lstFkey.Count == 0) { mTran.Status = PaymentTransactionStatus.Completed; } else { mTran.Status = PaymentTransactionStatus.NotComplete; } log.Info("Change Payment Status Einvoices by: " + HttpContext.User.Identity.Name + " Info-- GUID: " + mTran.id.ToString() + "Tổng số: " + (count) + ", gạch nợ thành công " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn"); mTran.Messages = "Tổng số: " + (count) + ", gạch nợ thành công " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn"; //thuc hien deliveriy IDeliver _deliver = IoC.Resolve <IDeliver>(); _deliver.Deliver(updateInv.ToArray(), currentCom); log.Info("paymentViaBlock end " + mTran.id); return(mTran); } mTran.Status = PaymentTransactionStatus.Failed; log.Info("Change Payment Status Einvoices by: " + HttpContext.User.Identity.Name + " Info-- GUID: " + mTran.id.ToString() + "Tổng số: " + (count) + " hóa đơn, gạch nợ fail " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn"); if (count == 0) { mTran.Messages = "File không đúng mẫu hoặc không chứa dữ liệu"; } else { mTran.Messages = "Tổng số: " + (count) + " hóa đơn, gạch nợ fail " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn"; } return(mTran); }
void tsbExportToExcelClick(object sender, EventArgs e) { try { SaveFileDialog saveXLS = new SaveFileDialog(); saveXLS.FileName = _currentProject.Name; saveXLS.DefaultExt = "xls"; saveXLS.Filter = "Microsoft Office Excel Workbook |(*.xls*)"; saveXLS.CheckFileExists = false; saveXLS.InitialDirectory = projectsPath; if (saveXLS.ShowDialog() == DialogResult.OK) { int totalRows = 0; FileStream stream = new FileStream(saveXLS.FileName, FileMode.OpenOrCreate); ExcelWriter writer = new ExcelWriter(stream); writer.BeginWrite(); writer.WriteCell(0, 0, "Station ID"); writer.WriteCell(0, 1, "Camera Manufacturer"); writer.WriteCell(0, 2, "Camera Model"); writer.WriteCell(0, 3, "Image Name"); writer.WriteCell(0, 4, "Date"); writer.WriteCell(0, 5, "Time"); writer.WriteCell(0, 6, "Pressure (inHg)"); writer.WriteCell(0, 7, "Temperature (Celsius)"); writer.WriteCell(0, 8, "Species"); writer.WriteCell(0, 9, "Count"); foreach (Station st in _currentProject.StationsList) { foreach (Sample smp in st.SamplesList) { totalRows++; writer.WriteCell(totalRows, 0, st.StationID); writer.WriteCell(totalRows, 1, smp.CameraManufacturer); writer.WriteCell(totalRows, 2, smp.CameraModel); writer.WriteCell(totalRows, 3, smp.ImageName); writer.WriteCell(totalRows, 4, smp.DateTime.ToShortDateString()); writer.WriteCell(totalRows, 5, smp.DateTime.ToShortTimeString()); writer.WriteCell(totalRows, 6, (double)smp.OCR_Observations_list[0].Value); //Press writer.WriteCell(totalRows, 7, (int)smp.OCR_Observations_list[1].Value); //Temp SpeciesObservation spc0 = (SpeciesObservation)smp.Species_Observations_list[0]; writer.WriteCell(totalRows, 8, (string)spc0.Value); //Species0 writer.WriteCell(totalRows, 9, (int)spc0.Count); //Count0 //more than 1 specie observation, duplicate the row changing that value if (smp.OCR_Observations_list.Count > 3) { for (int j = 3; j < smp.OCR_Observations_list.Count; j++) { totalRows++; writer.WriteCell(totalRows, 0, st.StationID); writer.WriteCell(totalRows, 1, smp.CameraManufacturer); writer.WriteCell(totalRows, 2, smp.CameraModel); writer.WriteCell(totalRows, 3, smp.ImageName); writer.WriteCell(totalRows, 4, smp.DateTime.ToShortDateString()); writer.WriteCell(totalRows, 5, smp.DateTime.ToShortTimeString()); writer.WriteCell(totalRows, 6, (double)smp.OCR_Observations_list[0].Value); //Press writer.WriteCell(totalRows, 7, (int)smp.OCR_Observations_list[1].Value); //Temp SpeciesObservation spcN = (SpeciesObservation)smp.Species_Observations_list[j]; writer.WriteCell(totalRows, 8, (string)spcN.Value); //Species0 writer.WriteCell(totalRows, 9, (int)spcN.Count); //Count0 } } } } writer.EndWrite(); stream.Close(); } } catch (Exception ex) { throw ex; } }