protected FileContentResult ExportExcelBase(DataTable table, string fileName, string strHeaderText = null)
{
string dt = DateTime.Now.ToString("yyyyMMddHHmmss");
string headerName = string.IsNullOrEmpty(fileName) ? dt : fileName;
if (string.IsNullOrEmpty(fileName))
{
fileName = dt + ".xls";
}
else
{
fileName = fileName + ".xls";
}
//ie 需要编码
if (string.Compare(Request.Browser.Browser, "IE", true) == 0 || string.Compare(Request.Browser.Browser, "InternetExplorer", true) == 0)
{
fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
}
var export = new ExcelExport();
return(File(export.ExportExcelFromDataTable(table, headerName).GetBuffer(),
"application/vnd.ms-excel", fileName));
}
public void ExportToExcel(string GridModel)
{
// IEnumerable datasource = db.A_Facilities.ToList();
ExcelExport exp = new ExcelExport();
GridProperties obj = ConvertGridObject(GridModel);
if (cat == "FacilityData")
{
currentData = db.View_A_Facility.ToList();
obj.Columns[1].DataSource = db.A_DeliveryZone.ToList();
obj.Columns[2].DataSource = db.A_ImplimentingPartners.ToList();
obj.Columns[3].DataSource = db.A_District.ToList();
obj.Columns[8].DataSource = db.A_Facility_Level_Of_Care.ToList();
obj.Columns[9].DataSource = db.A_ClientType.ToList();
obj.Columns[11].DataSource = db.A_Ownership.ToList();
obj.Columns[12].DataSource = db.A_CDCRegion.ToList();
obj.Columns[13].DataSource = db.A_FacilityType.ToList();
obj.Columns[16].DataSource = db.A_ImplimentingPartners.ToList();
obj.Columns[17].DataSource = db.A_PatientLoad.ToList();
//obj.Columns[21].DataSource = db.fo_SCTO.ToList();
}
//GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel);
exp.Export(obj, currentData, Module + ".xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron");
}
protected void Grid1_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)
{
ExcelExport exp = new ExcelExport();
//Items needs below assembly reference
// using Syncfusion.EJ.Export;
//using Syncfusion.XlsIO;
//Added manually, also it will add references to assembly in WEB.config file
DataView dv = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());
DataTable dt = dv.Table;
IWorkbook book = exp.Export(Grid1.Model, dt, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime", true);
//When the Grid has some null values from DB It causes exception while exporting, so better to avoid DBNull- An exception of type 'System.InvalidCastException' occurred in mscorlib.dll but was not handled in user code Additional information: Object cannot be cast from DBNull to other types.
// Inserted new row for adding title
book.ActiveSheet.InsertRow(1);
// Merging the sheet from Range A1 to D1 for adding title space
book.ActiveSheet.Range["A1:D1"].Merge();
//Adding the title using Text property
book.ActiveSheet.Range["A1"].Text = "Some Content Title";
book.ActiveSheet.Range["A1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;//set text alignment
//Adding footer using SetValue method
book.ActiveSheet.SetValue(book.ActiveSheet.Rows.Length + 2, book.ActiveSheet.Columns.Length - 3, "CopyRights");
book.SaveAs("Export.xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response, ExcelDownloadType.Open);
}
private string CreateExport(DataTable filtered_data, string company_name)
{
DateTime now = DateTime.Now;
// Initializing the column names for the export.
string[] column_names =
new string[] { "Employee ID", "Employee Name", "Punch Date", "In Time", "Out Time", "Status", "Company Name", "Branch Name", "Department Name", "Category Name", "Shift Name" };
//string
// user_id = HttpContext.Current.Session["employee_id"].ToString(),
// file_name = "DailyAttendanceReport-" + user_id + "-" + now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string importFile,
user_id = HttpContext.Current.Session["employee_id"].ToString(),
exportPath = "DailyAttendanceReport-" + user_id + "-" + now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
importFile = "DailyAttendanceReport_Template" + ".xlsx";
string importFilePath = HttpContext.Current.Server.MapPath("~/exports/templates/" + importFile);
ExcelExport.ExportDataToExcelWithLogo(importFilePath, "DAILY ATTENDANCE REPORT", filtered_data, Context, column_names, company_name, exportPath);
//ExcelExport.ExportDataToExcel(exportPath, "DAILY ATTENDANCE REPORT", filtered_data, Context, column_names, company_name);
return(exportPath);
}
private void btnExport_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string str = "Excel (*.xlsx)|*.xlsx";
saveFileDialog1.Filter = str;
SaveFileDialog saveFileDialog2 = saveFileDialog1;
if (saveFileDialog2.ShowDialog() != DialogResult.OK)
{
return;
}
AlarmInfoListResponse infoListResponse = LocalApi.GetAlarmInfoList(new AlarmInfoListRequest()
{
Keyword = this.Keyword, AlarmDateStart = this.AlarmDateStart, AlarmDateEnd = this.AlarmDateEnd, PageNumber = 1, PageSize = int.MaxValue
});
if (infoListResponse.IsError)
{
return;
}
PagedData <AlarmInfoModel> rows = infoListResponse.AlarmInfoModel;
ExcelExport excelExport = new ExcelExport();
DataTable table = ToDataTable(rows.ToList());
excelExport.sheetName = "新报警记录";
excelExport.ExcuteExport(saveFileDialog2.FileName, table);
}
private string CreateExport(DataTable ooo_data, int access_level)
{
DateTime now = DateTime.Now;
string
user_id = HttpContext.Current.Session["employee_id"].ToString(),
file_name = "OutOfOffice-" + user_id + "-" + now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
if (access_level == 1)
{
// Initializing the column names for the export.
string[] column_names =
new string[] { "Employee ID", "Employee Name", "OutOfOffice Type", "From DateTime", "To DateTime", "Hours", "TotalHours Availed", "Reason", "Manager Status" };
ExcelExport.ExportDataToExcel(file_name, "OUT OF OFFICE", ooo_data, Context, column_names);
}
if (access_level == 3)
{
// Initializing the column names for the export.
string[] column_names =
new string[] { "EmployeeID", "EmployeeName", "OutOfOffice Type", "FromDateTime", "ToDateTime", "Hours", "TotalHours Availed", "Reason", "Manager Name", "Manager Remark", "Manager Status", "HR Status" };
ExcelExport.ExportDataToExcel(file_name, "OUT OF OFFICE", ooo_data, Context, column_names);
}
return(file_name);
}
protected void FlatGrid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)
{
ExcelExport exp = new ExcelExport();
exp.Export(FlatGrid.Model, (IEnumerable)FlatGrid.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime");
}
private void itemExportToExcel_Click(object sender, EventArgs e)
{
var excel = new ExcelExport();
excel.SelectedProjects.Add(((ProjectTreeNode)treeView.SelectedNode).Project);
excel.ShowDialog();
}
private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.RowIndex < 0)
{
return;
}
exInfo = DAL.ExInfoDataFactory.Construct(exDT.Rows[e.RowIndex]);
List <DAL.NIChannelSettingData> aiList = new List <DAL.NIChannelSettingData>();
aiNameList = exInfo.NI9188Mod.Split('|');
string strSQL = "select * from NIChannelSetting where ( ";
foreach (string ai in aiNameList)
{
strSQL += "ID=" + ai + " OR ";
}
strSQL = strSQL.Substring(0, strSQL.Length - 4);
strSQL += ")";
aidt = WFNetLib.ADO.SQLServerOP.DataTableSQL(strSQL);
for (int i = 0; i < aidt.Rows.Count; i++)
{
DAL.NIChannelSettingData ai = DAL.NIChannelSettingDataFactory.Construct(aidt.Rows[i]);
aiList.Add(ai);
}
strSQL = "select * from ExData where ExID=" + exInfo.ID.ToString();
exData = WFNetLib.ADO.SQLServerOP.DataTableSQL(strSQL);
ExcelEx = new ExcelExport(exData.Rows.Count);
ExcelEx.ExcelWorkbookCallbackProc = new ExcelExport.ExcelWorkbookCallback(ExcelWorkbookCallbackProc);
ExcelEx.ExcelExportProc();
}
private byte[] GeneracionExcel(List <int> input)
{
try
{
ExcelExport excel = new ExcelExport();
byte[] arrBytes = { };
if (input.Exists(x => x.Equals(1)))
{
List <ClienteReporteDto> list = clientesRepository.GetAll().Select(x => new ClienteReporteDto
{
ID = x.ID.ToString(),
Nombre = x.Nombre,
Apellido = x.Apellido,
DniCuit = x.DniCuit.ToString(),
RazonSocial = x.RazonSocial,
Direccion = x.Direccion,
TelefonoContacto = x.TelefonoContacto.ToString(),
Email = x.Email
}).ToList();
arrBytes = excel.Export(list);
}
return(arrBytes);
}
catch (Exception ex)
{
throw new Exception("Error al generar el excel", ex);
}
}
protected void lnkExcelExport_OnClick(object sender, EventArgs e)
{
var dtWallet = GetWalletDt(ePageIndex.AllPage);
dtWallet.Columns[CS.CashTransactionId].ColumnName = CS.Id;
dtWallet.Columns[CS.EntryFullName].ColumnName = CS.Name;
dtWallet.Columns[CS.TransactionTime].ColumnName = CS.Date;
var lstColumns = new System.Collections.Generic.List <string>();
lstColumns.Add(CS.Id);
lstColumns.Add(CS.TransactionType);
lstColumns.Add(CS.Name);
lstColumns.Add(CS.Date);
lstColumns.Add(CS.DebitAmount);
lstColumns.Add(CS.CreditAmount);
lstColumns.Add(CS.Narration);
string FileName = "Transaction";
if (txtFromDate.zIsDate() && txtToDate.zIsDate())
{
FileName += " For(" + txtFromDate.zToDate().Value.ToString(CS.ddMMMyy) + "-" + txtToDate.zToDate().Value.ToString(CS.ddMMMyy) + ")";
}
ExcelExport.SetExportData(dtWallet, lstColumns, lstColumns, FileName);
popupExcelExport.Show();
}
/// <summary>
/// Exporta la información de un dataGridView a Excel de manera dinamica
/// </summary>
/// <param name="dataGridView">DataGridView de origen</param>
/// <param name="pFullPath_toExport">Ruta del archivo exportado</param>
/// <param name="nameSheet">Nombre de la hoja</param>
public void dataGridView2ExcelDinamico(Xceed.Wpf.DataGrid.DataGridControl dataGridView, string pFullPath_toExport, string nameSheet)
{
System.Data.DataTable dt = new System.Data.DataTable();
ExcelExport myExcel = new ExcelExport();
myExcel.CreateWorksheet(nameSheet);
//Recorro el DataGrid para buscar las columnas
foreach (Xceed.Wpf.DataGrid.Column Column in dataGridView.Columns)
{
dt.Columns.Add(Column.Title.ToString());
}
foreach (DataRow DataRow in dataGridView.Items)
{
DataRow dr = dt.NewRow();
foreach (Xceed.Wpf.DataGrid.Column Column in dataGridView.Columns)
{
dr[Column.Title.ToString()] = DataRow[Column.Title.ToString()];
}
dt.Rows.Add(dr);
}
myExcel.PopulateFromDataTable(nameSheet, dt);
myExcel.SaveToFile(pFullPath_toExport);
//dataTable2ExcelDinamico(dt, dataGridView, pFullPath_toExport, nameSheet);
}
public void InHD()
{
ExcelExport ex = new ExcelExport();
if (dgvCTHD.Rows.Count == 0)
{
MessageBox.Show("Khong co du lieu de Xuat");
return;
}
List <INHOADON> plistdiem = new List <INHOADON>();
int Stt = 1;
string path = "";
foreach (DataGridViewRow item in dgvCTHD.Rows)
{
INHOADON d = new INHOADON();
d.MANUOC = item.Cells[0].Value.ToString();
d.TENNUOC = item.Cells[1].Value.ToString();
d.SOLUONG = int.Parse(item.Cells[2].Value.ToString());
d.DONGIA = double.Parse(item.Cells[3].Value.ToString());
d.THANHTIEN = double.Parse(item.Cells[4].Value.ToString());
d.STT = Stt.ToString();
Stt++;
plistdiem.Add(d);
path = string.Empty;
ex.ExportKhoa(plistdiem, ref path, false, txtMaHD.Text, txtNgayLap.Text, cboNV.Text, cboBan.Text, double.Parse(txtTongTien.Text));
}
ex.OpenFile(path);
}
static void Main(string[] args)
{
try
{
// DI
IKernel _kernal = new StandardKernel();
_kernal.Bind<INLogger>().To<NLogger>().InSingletonScope();
_kernal.Bind<IRepo>().To<Repo>().InSingletonScope();
_kernal.Bind<IOutputHelper>().To<OutputHelper>().InSingletonScope();
_logger = _kernal.Get<NLogger>();
_repo = _kernal.Get<Repo>();
_output = _kernal.Get<OutputHelper>();
//ValidateRunLengths();
var duplicates = ValidateIRIAVG();
var export = new ExcelExport().AddSheet("Duplicates", duplicates.ToArray());
export.ExportTo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), System.Configuration.ConfigurationManager.AppSettings["excel:exportFileName"].ToString()));
}
catch (Exception ex)
{
_output.Write(string.Format("Error: {0}", ex.Message), true);
}
Console.WriteLine("Done. Press any key to exist.");
Console.ReadKey();
}
// GET: GestanteCita/ExportarExcel
public ActionResult ExportarExcel(FormCollection form)
{
try
{
string numDni = form["GestanteNroDocumento"];
string sortColumn = !string.IsNullOrWhiteSpace(form["hdSortColumn"]) ? form["hdSortColumn"] : "GestanteMonitoreoId";
string sortDirection = !string.IsNullOrWhiteSpace(form["hdSortDirection"]) ? form["hdSortDirection"] : "asc";
RegistrarAccion((byte)AccionSesion.ExportExcel, (byte)ObjetoSesion.ListaGestanteMonitoreo, null);
var listaGestanteMonitoreos = gestanteManager.ListarGestanteMonitoreos(numDni, sortColumn, sortDirection,
EstablecimientoRestriccion());
var listaGestanteMonitoreosExcel = from O in listaGestanteMonitoreos
select new
{
Id = O.GestanteMonitoreoId,
DNI = O.GestanteNroDocumento,
PresionSistolica = O.PresionSistolica,
Presion_Diastolica = O.PresionDiastolica,
Proteinuria = O.Proteinuria,
Movimientos_Fetales = O.MovimientosFetales,
Signos_Alarma = O.SignosAlarma,
Fecha_Registro = O.FechaRegistro.Value.ToShortDateString()
};
ExcelExport.ExportToSpreadsheet(listaGestanteMonitoreosExcel.CopyToDataTable(), "GestanteMonitoreos");
return(View());
}
catch (Exception e)
{
ViewBag.ErrorMessage = e;
return(PartialView("Error"));
}
}
public IHttpActionResult ExcelExport(TaxSavingModel ts)
{
/*string employeeId, string financialYear, string fyText*/
string fileName = Guid.NewGuid().ToString() + ".xlsx";
string physicalFileName = HttpRuntime.AppDomainAppPath + "/Log/" + fileName;
DataSet ds = TaxSavingsRepository.GetTaxSavingReceipt(ts.EmployeeId, ts.FinancialYear);
DataSet dsList = new DataSet();
List <string> strTableHeader = new List <string>();
System.Data.DataView view = new System.Data.DataView(ds.Tables[0]);
DataTable distinctValues = view.ToTable(true, "EmployeeId", "FullName");
for (int i = 0; i < distinctValues.Rows.Count; i++)
{
DataSet objDsList = TaxSavingsRepository.GetTaxSavingReceiptForExcel((distinctValues.Rows[i]["EmployeeId"].ToString() != "") ? Guid.Parse(distinctValues.Rows[i]["EmployeeId"].ToString()) : Guid.Parse("00000000-0000-0000-0000-000000000000"), ts.FinancialYear);
objDsList.Tables[0].TableName = distinctValues.Rows[i]["FullName"].ToString();
dsList.Tables.Add(objDsList.Tables[0].Copy());
strTableHeader.Add(distinctValues.Rows[i]["FullName"].ToString());
}
var excelHelper = new ExcelExport();
Attachment objExcelFile = new Attachment();
string excelSheetHeading = "Tax Savings (FY " + ts.fyText + ")";
excelHelper.ExportToExcel(physicalFileName, dsList, excelSheetHeading);
string downloadName = string.Format("Tax Savings -{0:yyyyMMdd-HHmmss}" + ".xlsx", DateTime.Now);
objExcelFile.FileName = downloadName;;
objExcelFile.FileContent = System.IO.File.ReadAllBytes(physicalFileName);
objExcelFile.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return(Ok(objExcelFile));
}
public async Task ExportToExcel(string GridModel, string gridId, int id, string survey)
{
ExcelExport exp = new ExcelExport();
GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel);
//Clear if there are any filter columns
//syncfusion bug in exporting while in filter mode
obj.FilterSettings.FilteredColumns.Clear();
grid = gridId;
count = 0;
if (gridId == "Surveys")
{
var surveys = await _prepareService.GetSurveys();
var model = QuestionnaireMapper.ToSurveyManageViewModel(surveys);
var dataSource = model.Surveys.ToList();
var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-");
exp.Export(obj, dataSource, LocalizedStrings.GetString("Questionnaire") + " " /*"Questionnaire "*/ + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron");
}
if (gridId == "SurveyItems")
{
var surveys = await _prepareService.GetSurveys(id);
var data = surveys.FirstOrDefault();
var model = QuestionnaireMapper.ToSurveyViewModel(data);
var dataSource = model.SurveyItems.ToList();
var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-");
exp.Export(obj, dataSource, LocalizedStrings.GetString("Questionnaire") + " " /*"Questionnaire "*/ + survey + " " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron");
}
}
public void Should_throw_exception_if_OutputToStream_called_before_any_data_written()
{
MemoryStream outputStream = new MemoryStream();
ExcelExport target = new ExcelExport();
Assert.Throws <InvalidOperationException>(() => target.ExportToStream(outputStream));
}
protected void EmployeesGrid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)
{
ExcelExport exp = new ExcelExport();
IWorkbook workbook = exp.Export(EmployeesGrid.Model, (IEnumerable)EmployeesGrid.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime", true);
exp.Export(OrdersGrid.Model, (IEnumerable)OrdersGrid.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime", false, workbook, MultipleExportType.AppendToSheet, "Orders Grid");
}
public IExport Create(string exportFormat)
{
IExport export;
_log.Info("Selected export format - " + exportFormat);
switch (exportFormat)
{
case "csv":
_log.Debug("Create CSVExport");
export = new CSVExport(_log);
break;
case "xlsx":
_log.Debug("Create CSVExport");
export = new ExcelExport(_log);
break;
default:
_log.Warn("Wrong exportFormat, select CSVExport");
export = new CSVExport(_log);
break;
}
return(export);
}
protected override void WriteFile(HttpResponseBase response)
{
var type = ViewModelDescriptor.GetTypeDescriptor(_itemType);
Grid grid = new Grid();
var user = _securityHelper.GetCurrentUser();
var properties = type.Properties.Where(x => x.Show && (user == null || x.Permission.Check(user.CurrentUser))).OrderBy(x => x.Order).ToList();
foreach (var propertyDescriptor in properties)
{
grid.Columns.Add(new GridColumn(propertyDescriptor.ShortName, 80));
}
foreach (var item in _items)
{
var row = grid.NewRow();
for (int i = 0; i < properties.Count; i++)
{
var propertyDescriptor = properties[i];
row[i].Text = string.Format(propertyDescriptor.ShortNameFormat, propertyDescriptor.GetValue(item));
}
grid.Rows.Add(row);
}
ExcelExport export = new ExcelExport();
var bytes = export.Export(grid);
response.BinaryWrite(bytes);
response.End();
}
// To handle the excel file
private void buttonAdv2_Click(object sender, EventArgs e)
{
var ExportAsPivotTable = (comboBoxAdv1.SelectedIndex == 0);
SaveFileDialog savedialog = new SaveFileDialog();
savedialog.AddExtension = true;
savedialog.FileName = "Sample";
savedialog.DefaultExt = "xlsx";
savedialog.Filter = @"Excel file (.xlsx)|*.xlsx";
if (savedialog.ShowDialog() == DialogResult.OK)
{
ExcelExport excelExport = new ExcelExport(pivotGridControl1, ExcelVersion.Excel2010);
excelExport.ExportMode = (ExportAsPivotTable) ? ExportModes.PivotTable : ExportModes.Cell;
excelExport.Export(savedialog.FileName);
if (MessageBox.Show(@"Export Success! Do you want to open the exported file?", Text, MessageBoxButtons.YesNo) == DialogResult.Yes)
#if !NETCORE
{ Process.Start(savedialog.FileName); }
#else
{
var p = new Process();
p.StartInfo = new ProcessStartInfo(savedialog.FileName)
{
UseShellExecute = true
};
p.Start();
}
#endif
}
}
void button1_Click(object sender, EventArgs e)
{
SaveFileDialog savedialog = new SaveFileDialog();
savedialog.AddExtension = true;
savedialog.FileName = "Sample";
savedialog.DefaultExt = "xlsx";
savedialog.Filter = "Excel file (.xlsx)|*.xlsx";
if (savedialog.ShowDialog() == DialogResult.OK)
{
ExcelExport excelExport = new ExcelExport(this.pivotChart1, Syncfusion.XlsIO.ExcelVersion.Excel2010, Syncfusion.XlsIO.ExcelChartType.Column_Clustered);
excelExport.Export(savedialog.FileName);
if (MessageBox.Show("Export Success! Do you want to open the exported file?", this.Text, MessageBoxButtons.YesNo) == DialogResult.Yes)
{
#if !NETCORE
Process.Start(savedialog.FileName);
#else
var p = new Process();
p.StartInfo = new ProcessStartInfo(savedialog.FileName)
{
UseShellExecute = true
};
p.Start();
#endif
}
}
}
protected override void WriteFile(HttpResponseBase response)
{
Grid grid = new Grid();
var properties = _table.Columns;
foreach (DataColumn propertyDescriptor in properties)
{
grid.Columns.Add(new GridColumn(propertyDescriptor.Caption, 80));
}
foreach (DataRow item in _table.Rows)
{
var row = grid.NewRow();
for (int i = 0; i < properties.Count; i++)
{
var propertyDescriptor = properties[i];
row[i].Text = item[i];
}
grid.Rows.Add(row);
}
ExcelExport export = new ExcelExport();
var bytes = export.Export(grid);
response.BinaryWrite(bytes);
response.End();
}
public async Task <IHttpActionResult> ExportTemplate()
{
ResponseDataDTO <department> response = new ResponseDataDTO <department>();
try
{
var list = new List <department>();
Dictionary <string, string> dicColNames = GetImportDicColumsTemplate();
string url = "";
string filePath = GenExcelExportFilePath(string.Format(typeof(department).Name), ref url);
ExcelExport.ExportToExcelFromList(list, dicColNames, filePath, string.Format("Department"));
response.Code = HttpCode.NOT_FOUND;
response.Message = "Đã xuất excel thành công!";
response.Data = null;
}
catch (Exception ex)
{
response.Code = HttpCode.INTERNAL_SERVER_ERROR;
response.Message = ex.Message;;
response.Data = null;
Console.WriteLine(ex.ToString());
return(Ok(response));
}
return(Ok(response));
}
private void SalesmanReport()
{
if (SelectedSalesman == null)
{
return;
}
SaveFileDialog saveFileDialog = new SaveFileDialog {
Filter = "Excel files (*.xlsx)|*.xlsx", InitialDirectory = Directory.GetCurrentDirectory()
};
saveFileDialog.ShowDialog();
if (!saveFileDialog.FileName.EndsWith(".xlsx"))
{
//MessageBox.Show("Файл для сохранения не выбран", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
ExcelExport export = new ExcelExport();
export.SalesmanReport(saveFileDialog.FileName, SelectedSalesman.ID, FromDate, ThroughDate, _context);
MessageBox.Show("Отчет готов");
}
public void MultipleExportToExcel(string[] TreeGridModel)
{
ExcelExport exp = new ExcelExport();
IWorkbook workbook = null;
int count = 1;
foreach (string gridProperty in TreeGridModel)
{
TreeGridProperties gridProp = this.ConvertTreeGridObject(gridProperty);
if (count == 1)
{
workbook = exp.Export(gridProp, this.GetPlanDataSource(), new TreeGridExportSettings()
{
Theme = ExportTheme.FlatSaffron
}, true, "Planning Phase");
}
else if (count == 2)
{
exp.Export(gridProp, this.GetDesignDataSource(), new TreeGridExportSettings()
{
Theme = ExportTheme.FlatSaffron
}, workbook, true, "Design Phase");
}
else
{
exp.Export(gridProp, this.GetImplementationDataSource(), "ExcelExport.xlsx", ExcelVersion.Excel2010, new TreeGridExportSettings()
{
Theme = ExportTheme.FlatSaffron
}, workbook, false, "Implementation Phase");
}
count++;
}
}
/// <summary>
/// 门店销售报表导出
/// </summary>
/// <returns></returns>
public FileResult ExportStoresSaleExcel()
{
var data = ReportBLL.GetStoreSaleSummary(new GuiderPerformanceRptModel
{
GuiderID = Request["GuiderID"],
QueryStartDate = Request["QueryStartDate"],
QueryEndDate = Request["QueryEndDate"],
StoreID = Request["StoreID"]
});
List <string> exportFields = new List <string> {
"StoreID", "StoreName", "GoodsAmount", "GoodsNum", "RefundAmount", "ReturnGoodsNum", "CardMode", "WeChatMode", "AlipayMode", "CashMode", "MarketMode"
};
if (data == null || !data.Any())
{
data = new List <StoreSaleRptList>()
{
new StoreSaleRptList {
StoreID = "合计", GoodsAmount = 0m, GoodsNum = 0, RefundAmount = 0m, ReturnGoodsNum = 0
}
};
}
System.IO.MemoryStream memory = ExcelExport.ExportMemory(data, exportFields);
return(File(memory, "application/vnd.ms-excel", $"门店销售统计报表{DateTime.Now.ToString("yyyyMMddHH")}.xls"));
}
/// <summary>
/// Сформировать файл с выгрузкой и отправить
/// </summary>
/// <param name="excelExport"></param>
/// <param name="FileName"></param>
/// <param name="Caption"></param>
/// <returns></returns>
private async Task <IActionResult> SendExportFile(ExcelExport excelExport, string FileName, string Caption = "")
{
if (CheckTime())
{
ExcelExport = excelExport;
base.SendAction();
RequestLogFunction.Insert(FollowerId, DateTime.Now);
await base.SendDocument(new FileToSend { Content = ExcelExport.BuildReport(), Filename = FileName }, Caption);
return(OkResult);
}
else
{
if (Update.CallbackQuery == null)
{
await SendMessage(new BotMessage { TextMessage = "Не более одного запроса в минуту" });
}
else
{
await AnswerCallback("Не более одного запроса в минуту", true);
}
return(OkResult);
}
}
private void btnExport_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string str = "Excel (*.xlsx)|*.xlsx";
saveFileDialog1.Filter = str;
SaveFileDialog saveFileDialog2 = saveFileDialog1;
if (saveFileDialog2.ShowDialog() != DialogResult.OK)
{
return;
}
ZhuiSuResponse zhuisuResponse = LocalApi.ExecuteHistory(new ZhuiSuRequest()
{
ProcessDID = this.ProcessDID,
//PageNumber = this.pager.PageNumber,
TimeStart = this.TimeStart,
TimeEnd = this.TimeEnd,
PageNumber = 1,
PageSize = int.MaxValue
});
if (zhuisuResponse.IsError)
{
return;
}
PagedData <ZhuiSuItem> rows = zhuisuResponse.ZhuiSus;
ExcelExport excelExport = new ExcelExport();
DataTable table = ToDataTable(rows.ToList());
excelExport.sheetName = "历史追溯记录";
excelExport.ExcuteExport(saveFileDialog2.FileName, table);
}
public void ExportToExcel(string GridModel)
{
ExcelExport exp = new ExcelExport();
var DataSource = new NorthwindDataContext().OrdersViews.Take(100).ToList();
GridProperties obj = ConvertGridObject(GridModel);
exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron");
}
public void ExportToExcelGenero(string GridModel)
{
ExcelExport exp = new ExcelExport();
var DataSource = context.Generoes.ToList();
GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel);
exp.Export(obj, DataSource, "ExcelGenero.xlsx", Syncfusion.XlsIO.ExcelVersion.Excel2010, false, false, "flat-saffron");
}
public static Row GetRowData(ExcelExport sut, int rowIndex)
{
using (MemoryStream output = new MemoryStream())
{
sut.ExportToStream(output);
HSSFWorkbook workbook = new HSSFWorkbook(output);
return workbook.GetSheetAt(0).GetRow(rowIndex);
}
}
public void Should_return_stream_with_data()
{
MemoryStream outputStream = new MemoryStream();
ExcelExport target = new ExcelExport();
CellFormatter formatter = new CellFormatter();
target.WriteDataToExport(sampleData, formatter,ExcelFileType.XLS);
target.ExportToStream(outputStream);
Assert.IsTrue(outputStream.Length > 0);
}
public void Setup()
{
sut = new ExcelExport();
formatter = new CellFormatter();
workBook = Helper.CreateWorkBookWithSheet();
sampleData = new List<List<Cell>>
{
new List<Cell>
{
new Cell {Value = "1"},
new Cell {Value = "2"},
new Cell {Value = "a"},
new Cell {Value = "b"},
}
};
}
public void Should_be_able_to_pass_data_to_be_written_to_workbook()
{
ExcelExport target = new ExcelExport();
Workbook book = Helper.CreateWorkBookWithSheet();
List<List<Cell>> cells = new List<List<Cell>>
{
new List<Cell>
{
new Cell {Value = "123"}
}
};
CellFormatter formatter = Substitute.For<CellFormatter>();
formatter.WriteDataToSheet(book.GetSheetAt(0), cells);
formatter.Received().WriteDataToSheet(book.GetSheetAt(0), cells);
}
private static void Main(string[] args)
{
//get the currently logged in user
WindowsIdentity user = WindowsIdentity.GetCurrent();
WindowsPrincipal principal = new WindowsPrincipal(user);
Console.WriteLine(principal.IsInRole(WindowsBuiltInRole.Administrator));
Console.ReadLine();
Context context = new Context();
using (context)
{
ExcelExport excel = new ExcelExport();
excel.GeneralReport("E:\\report.xlsx", DateTime.MinValue, DateTime.MaxValue, context);
Console.WriteLine("DONE");
}
}
private void exportCulturePair_Click(object sender, EventArgs e)
{
var node = (CultureAnalysisResultTreeNode)treeView.SelectedNode;
var excel = new ExcelExport();
excel.SelectedCultures.Add(node.SourceCulture);
excel.SelectedCultures.Add(node.TargetCulture);
excel.Solution = Main.CurrentSolution;
excel.ShowDialog();
}
//导出表格
public ActionResult Export(int? projectid, string itemid, FormCollection collection)
{
int IsStandard = new ProjectDao().Find(projectid.Value).IsStandard;
Hashtable ht = new Hashtable();
ht["pid"] = projectid.Value;
ht["itemid"] = itemid;
IDictionary medicineinfo = (IsStandard == 1) ? StandardDataMapper.Get().QueryForObject<IDictionary>("CompareData.ListSingleName", itemid) : EmedEvaluationProjectMapper.Get().QueryForObject<IDictionary>("CompareDataProject.ListSingleName", ht);
if (collection.Count != 0 && collection["excel"] == "1")
{
ExcelExport mx = new ExcelExport();
mx.InitTh();
mx._fileName = medicineinfo["ChineseProductName"].ToString() + DateTime.Now.ToShortDateString() + ".xls";
//项目名称
TableItemStyle tiName = new TableItemStyle();
tiName.Font.Size = 14;
tiName.ForeColor = System.Drawing.Color.Red;
tiName.HorizontalAlign = HorizontalAlign.Center;
mx.HeadExtrInfo.Add("评价药品：" + medicineinfo["ChineseProductName"] + medicineinfo["Formulation"] + medicineinfo["ChineseSpecification"] + medicineinfo["ChineseManufactureName"], tiName);
//得到第二行表头名称
TableItemStyle tiSecondName = new TableItemStyle();
tiSecondName.Font.Size = 14;
tiSecondName.ForeColor = System.Drawing.Color.Black;
tiSecondName.HorizontalAlign = HorizontalAlign.Center;
mx.SecondExtrInfo.Add(collection["secondhead"], tiSecondName);
string[] spanlist = collection["spanlist"].Split(',');
string[] header = collection["header"].Split(',');
string[] bodycontent = collection["content"].Split('|');
List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
foreach (string bc in bodycontent)
{
string[] each = bc.Split('^');
Dictionary<string, string> contentdict = new Dictionary<string, string>();
int count = 0;
foreach (string e in each)
{
contentdict.Add(header[count], e);
count++;
}
list.Add(contentdict);
}
DataTable data = new DataTable();
data = ConvertTo(header, list);
mx.DataTableToExcelForSimulate(data, spanlist);
}
return View();
}
public ActionResult Detail(int id, int? pageNo, string parm, FormCollection query)
{
Dictionary<string, string> sitemaster = GetSiteMaster();
ViewData["SiteMaster"] = sitemaster;
int uid = Int32.Parse(sitemaster["userid"]);
if (sitemaster["istest"] == "1")
{
IList<int> openprojectlist = CRMMapper.Get().QueryForList<int>("OpenBidNumForTest.myselectopen", uid);
//先检查一共看的数目
if (openprojectlist.Count > 0)
{
//如果有看得记录，先检查是否是看过的
Hashtable htt = new Hashtable();
htt["pid"] = id;
htt["uid"] = uid;
IList<int> sawid = CRMMapper.Get().QueryForList<int>("OpenBidNumForTest.myselectopenpid", htt);
//如果没看过，就检查是否超过最大值
if (sawid.Count == 0)
{
if (openprojectlist.Count < 5)
{
//没超过就写入记录
OpenBidNumForTest ob = new OpenBidNumForTest();
ob.OpenprojectNum = id;
ob.UserID = uid;
new OpenBidNumForTestDao().Insert(ob);
}
else
{
//超过了
return RedirectToAction("List");
}
}
}
else
{
//之前没有看过也写入记录
OpenBidNumForTest ob = new OpenBidNumForTest();
ob.OpenprojectNum = id;
ob.UserID = uid;
new OpenBidNumForTestDao().Insert(ob);
}
}
DOpenProjectDao mDOpenProjectDao = new DOpenProjectDao();
DOpenProject mDOpenProject = mDOpenProjectDao.Find(id);
int interval = 0;
if (EnterPriseMemberInfo.Memberlevel == 201)
interval = 6;
if (EnterPriseMemberInfo.Memberlevel == 301)
interval = 12;
DateTime limitedate = DateTime.Now.AddMonths(interval);
if ((EnterPriseMemberInfo.Memberlevel == 201 || EnterPriseMemberInfo.Memberlevel == 301) && mDOpenProject.OpenDate > limitedate)
{
return RedirectToAction("MemberLevelError", "Base");
}
ViewData["ProjectDescription"] = mDOpenProject.NewProjectDescription;
ViewData["Summary"] = mDOpenProject.Summary;
ViewData["OID"] = mDOpenProject.OID;
Dictionary<string, string> dict = ParmHelper.Analysis(query);
//如果有参数
if (dict.Count != 0)
{
string parmeter = ParmHelper.BuildParm(dict);
return RedirectToAction("Detail", new { id = id, pageNo = 1, parm = parmeter });
}
else
{
Hashtable memberkeys = new Hashtable();
int PageNo = pageNo ?? 1;
if (parm != null)
{
dict = ParmHelper.AnalysisParm(parm);
Hashtable ht = new Hashtable();
ht.Add("ID", dict["CommonName"]);
ht.Add("UserID", uid);
memberkeys = CRMMapper.Get().QueryForObject<Hashtable>("MemberMedicineKey.FindByID", ht);
dict["IsSearched"] = "1";
}
else
{
memberkeys.Add("KeyList", "");
dict.Add("excel", "");
}
string commonnames = memberkeys["KeyList"].ToString().Replace(" ", "").Replace(" ", "");
//页码与记录数
int count = 0;
//是否竞品条件
bool compete = false;
//取数据与记录数
List<string> th = new List<string>();
DOpenMedicineDao m = new DOpenMedicineDao();
DataTable data = m.GetExportData(id, PageNo, PageSizeDetail, commonnames, out count, ref th, dict["excel"]);
#region 数据处理
if (data == null)
{
data = new DataTable();
}
if (!data.Columns.Contains("ID"))
{
DataColumn dc = new DataColumn();
dc.ColumnName = "ID";
data.Columns.Add(dc);
}
int beginNo = 0;
if (!compete)
{
beginNo = (PageNo - 1) * PageSizeDetail;
}
for (int i = 1; i <= data.Rows.Count; i++)
{
data.Rows[i - 1]["ID"] = (beginNo + i).ToString();
}
if (data.Columns.Contains("ROWID"))
{
data.Columns.Remove("ROWID");
}
if (data.Columns.Contains("OID"))
{
data.Columns.Remove("OID");
}
List<DataRow> listRow = new List<DataRow>();
foreach (DataRow dr in data.Rows)
{
listRow.Add(dr);
}
PagedList<DataRow> list;
if (compete)
{
list = new PagedList<DataRow>(listRow, PageNo, PageSizeDetail);
}
else
{
list = new PagedList<DataRow>(listRow, PageNo, PageSizeDetail, count);
}
#endregion
bool isExcel = dict["excel"] == "1" ? true : false;
ViewData["Query"] = dict;
ViewData["Count"] = count;
ViewData["Th"] = th;
ViewData["KeyList"] = memberkeys["KeyList"];
IList<IDictionary> ownkeys = CRMMapper.Get().QueryForList<IDictionary>("MemberMedicineKey.FindByUserID", uid);
ViewData["OwnKeys"] = ownkeys;
#region excel
if (sitemaster["istest"] == "0" && isExcel)
{
ExcelExport mx = new ExcelExport();
mx.InitTh();
mx._fileName = memberkeys["ListName"] + DateTime.Now.ToShortDateString() + ".xls";
//if (query.CommonName != null)
// mx._fileName = query.CommonName + DateTime.Now.ToShortDateString() + ".xls";
//else
//{
// if (query.ProductEnterpriseName != null)
// mx._fileName = query.ProductEnterpriseName + DateTime.Now.ToShortDateString() + ".xls";
// else
// mx._fileName = query.TenderEnterpriseName + DateTime.Now.ToShortDateString() + ".xls";
//}
//项目名称
TableItemStyle tiName = new TableItemStyle();
tiName.Font.Size = 14;
tiName.ForeColor = System.Drawing.Color.Red;
tiName.HorizontalAlign = HorizontalAlign.Center;
mx.HeadExtrInfo.Add(mDOpenProject.NewProjectName, tiName);
//项目备注
TableItemStyle tiRemark = new TableItemStyle();
tiRemark.Font.Size = 13;
tiRemark.ForeColor = System.Drawing.Color.Red;
tiRemark.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add("项目备注说明:", tiRemark);
//项目备注
TableItemStyle tiRemarkContent = new TableItemStyle();
tiRemarkContent.Font.Size = 12;
tiRemarkContent.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add(mDOpenProject.NewProjectDescription, tiRemarkContent);
TableItemStyle tiCount = new TableItemStyle();
tiCount.Font.Size = 12;
tiCount.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add("共有" + count.ToString() + "条", tiCount);
if (data.Columns.Contains("ID"))
{
data.Columns.Remove("ID");
}
//20111019修改按显示表头导出,去掉多余列
if (data.Columns.Contains("Mine"))
{
data.Columns.Remove("Mine");
}
mx._thName = th.ToArray();//20111019修改按显示表头导出
mx.DataTableToExcel(data);
}
#endregion
return View(list);
}
}
private void ExportToExcelFile()
{
var xlsExp = new ExcelExport<AnswerPointModel>(AnswerPointData);
xlsExp.ExportToFile(Product + "_answerpoint_" + StartDateValue.Replace("/", "") + "-" + EndDateValue.Replace("/", ""));
}
protected void btnExport_NoImage_Click(object sender, EventArgs e)
{
ExcelExport export = new ExcelExport("没有图片的产品" + DateTime.Now.ToString("yyyyMMdd-HHmmss"));
export.ExportProductExcel(ProductsNoImages);
lblMsg.Text = "操作完成";
}
private void itemExportToExcel_Click(object sender, EventArgs e)
{
var excel = new ExcelExport();
excel.SelectedProjects.Add(((ProjectTreeNode)treeView.SelectedNode).Project);
excel.ShowDialog();
}
public void Should_be_able_to_create_instance_of_class()
{
ExcelExport target = new ExcelExport();
Assert.IsNotNull(target);
}
protected void btnNtscodeExportExcel_Click(object sender, EventArgs e)
{
ExcelExport export = new ExcelExport("NTSCode列表" + DateTime.Now.ToString("yyyyMMdd-HHmmss"));
//foreach (Product p in ProductListForNtscodeList)
//{
// tbxNtscodeList.Text += p.Name + Environment.NewLine;
//}
export.ExportProductExcel(ProductListForNtscodeList);
lblMsg.Text = "操作完成";
}
public void Should_throw_exception_if_null_stream_provided()
{
ExcelExport target = new ExcelExport();
Assert.Throws<ArgumentNullException>(() => target.ExportToStream(null));
}
public void Should_throw_exception_if_OutputToStream_called_before_any_data_written()
{
MemoryStream outputStream = new MemoryStream();
ExcelExport target = new ExcelExport();
Assert.Throws<InvalidOperationException>(() => target.ExportToStream(outputStream));
}
private void ExportToExcelFile()
{
var xlsExp = new ExcelExport<UTModel>(UTData);
xlsExp.ExportToFile(Product + "_ut_" + StartDateValue.Replace("/","") + "-" + EndDateValue.Replace("/", ""));
}
private void exportCulture_Click(object sender, EventArgs e)
{
var node = (CultureTreeNode)treeView.SelectedNode;
var excel = new ExcelExport();
excel.SelectedCultures.Add(node.Culture);
excel.Solution = Main.CurrentSolution;
excel.ShowDialog();
}
private void ExportExcel(IList<Product> productList)
{
ExcelExport export = new ExcelExport("NTS_" + DateTime.Now.ToString("yyyyMMdd-HHmmss"), needInsertImage);
// IList<Product> pss = bizProduct.GetListByNTSCodeList(tbxCodeList.Text.Split(Environment.NewLine.ToCharArray()));
export.ExportProductExcel(productList);
// NLibrary.Notification.Show(this, "操作完成", "操作已经完成", string.Empty);
}
private void exportFileGroup_Click(object sender, EventArgs e)
{
var node = (ResourceFileGroupTreeNode)treeView.SelectedNode;
var excel = new ExcelExport();
excel.SelectedFileGroups.Add(node.FileGroup);
excel.Solution = Main.CurrentSolution;
excel.ShowDialog();
}
private void ExportToExcelFile()
{
var xlsExp = new ExcelExport<MetricsModel>(MetricsData);
xlsExp.ExportToFile(Product + "_metrics_" + StartDateValue.Replace("/", "") + "-" + EndDateValue.Replace("/", ""));
}
public ActionResult Detail(int id, int? pageNo, string parm, OpenBidProjectQuery query)
{
Dictionary<string, string> sitemaster = GetSiteMaster();
ViewData["SiteMaster"] = sitemaster;
//SingleProjectDao mDOpenProjectDao = new SingleProjectDao();
SingleProject mDOpenProject = EmedDataCenterMapper.Get().QueryForObject<SingleProject>("SingleProject.Find", id);
ViewData["ProjectDescription"] = mDOpenProject.NewProjectDescription;
ViewData["Summary"] = mDOpenProject.Summary;
ViewData["OID"] = mDOpenProject.OID;
//如果有参数
if (query.IsHaveProperty())
{
parm = query.GetParameter();
ViewData["Query"] = query;
return RedirectToAction("Detail", new { id = id, pageNo = 1, parm = parm });
}
else
{
int PageNo = pageNo ?? 1;
//表明第一次进来
if (query.CommonName != null)
{
query.LoadProperties(parm);
}
if (string.IsNullOrEmpty(query.CommonName) && string.IsNullOrEmpty(query.ProductEnterpriseName) && string.IsNullOrEmpty(query.TenderEnterpriseName) && query.CommonName.Length < 3)
{
pageNo = 1;
}
else
{
query.IsSearched = "1";
}
//页码与记录数
int count = 0;
//是否竞品条件
bool compete = false;
//取数据与记录数
List<string> th = new List<string>();
SingleMedicineDao m = new SingleMedicineDao();
DataTable data = m.GetData(id, PageNo, PageSizeDetail, query, out count, ref compete, ref th);
#region 数据处理
if (data == null)
{
data = new DataTable();
}
if (!data.Columns.Contains("ID"))
{
DataColumn dc = new DataColumn();
dc.ColumnName = "ID";
data.Columns.Add(dc);
}
int beginNo = 0;
if (!compete)
{
beginNo = (PageNo - 1) * PageSizeDetail;
}
for (int i = 1; i <= data.Rows.Count; i++)
{
data.Rows[i - 1]["ID"] = (beginNo + i).ToString();
}
if (data.Columns.Contains("ROWID"))
{
data.Columns.Remove("ROWID");
}
if (data.Columns.Contains("OID"))
{
data.Columns.Remove("OID");
}
List<DataRow> listRow = new List<DataRow>();
foreach (DataRow dr in data.Rows)
{
listRow.Add(dr);
}
foreach (DataRow dr in listRow)
{
if (dr.Table.Columns.Contains("FirstToLimit"))
{
string strFirstToLimit = dr["FirstToLimit"].ToString();
if (strFirstToLimit.Length > 0)
{
string subFirstToLimit = strFirstToLimit.Substring(0, strFirstToLimit.Length - 1);
decimal fFirstToLimit = decimal.Parse(subFirstToLimit);
string resultFirstToLimit = Math.Round(fFirstToLimit, 2).ToString() + "%";
dr["FirstToLimit"] = resultFirstToLimit;
}
}
if (dr.Table.Columns.Contains("SecondToLimit"))
{
string strSecondToLimit = dr["SecondToLimit"].ToString();
if (strSecondToLimit.Length > 0)
{
string subSecondToLimit = strSecondToLimit.Substring(0, strSecondToLimit.Length - 1);
decimal fSecondToLimit = decimal.Parse(subSecondToLimit);
string resultSecondToLimit = Math.Round(fSecondToLimit, 2).ToString() + "%";
dr["SecondToLimit"] = resultSecondToLimit;
}
}
if (dr.Table.Columns.Contains("SSecondToFirst"))
{
string strSSecondToFirst = dr["SSecondToFirst"].ToString();
if (strSSecondToFirst.Length > 0)
{
string subSSecondToFirst = strSSecondToFirst.Substring(0, strSSecondToFirst.Length - 1);
decimal fSSecondToFirst = decimal.Parse(subSSecondToFirst);
string resultSSecondToFirst = Math.Round(fSSecondToFirst, 2).ToString() + "%";
dr["SSecondToFirst"] = resultSSecondToFirst;
}
}
if (dr.Table.Columns.Contains("SBidToFinal"))
{
string strSBidToFinal = dr["SBidToFinal"].ToString();
if (strSBidToFinal.Length > 0)
{
string subSBidToFinal = strSBidToFinal.Substring(0, strSBidToFinal.Length - 1);
decimal fSBidToFinal = decimal.Parse(subSBidToFinal);
string resultSBidToFinal = Math.Round(fSBidToFinal, 2).ToString() + "%";
dr["SBidToFinal"] = resultSBidToFinal;
}
}
if (dr.Table.Columns.Contains("SBidToLimit"))
{
string strSBidToLimit = dr["SBidToLimit"].ToString();
if (strSBidToLimit.Length > 0)
{
string subSBidToLimit = strSBidToLimit.Substring(0, strSBidToLimit.Length - 1);
decimal fSBidToLimit = decimal.Parse(subSBidToLimit);
string resultSBidToLimit = Math.Round(fSBidToLimit, 2).ToString() + "%";
dr["SBidToLimit"] = resultSBidToLimit;
}
}
if (dr.Table.Columns.Contains("STBidToTender"))
{
string strSTBidToTender = dr["STBidToTender"].ToString();
if (strSTBidToTender.Length > 0)
{
string subSTBidToTender = strSTBidToTender.Substring(0, strSTBidToTender.Length - 1);
decimal fSBidToTender = decimal.Parse(subSTBidToTender);
string resultSfSBidToTender = Math.Round(fSBidToTender, 2).ToString() + "%";
dr["STBidToTender"] = resultSfSBidToTender;
}
}
}
PagedList<DataRow> list;
if (compete)
{
list = new PagedList<DataRow>(listRow, PageNo, PageSizeDetail);
}
else
{
list = new PagedList<DataRow>(listRow, PageNo, PageSizeDetail, count);
}
ViewData["Compete"] = compete;
ViewData["Query"] = query;
ViewData["Count"] = count;
ViewData["Th"] = th;
#endregion
#region excel
if (sitemaster["istest"]=="0" && query.Excel == "1")
{
ExcelExport mx = new ExcelExport();
mx.InitTh();
mx._fileName = "Emed-KB-" + DateTime.Now.ToShortDateString() + ".xls";
//项目名称
TableItemStyle tiName = new TableItemStyle();
tiName.Font.Size = 14;
tiName.ForeColor = System.Drawing.Color.Red;
tiName.HorizontalAlign = HorizontalAlign.Center;
mx.HeadExtrInfo.Add(mDOpenProject.NewProjectName, tiName);
//项目备注
TableItemStyle tiRemark = new TableItemStyle();
tiRemark.Font.Size = 13;
tiRemark.ForeColor = System.Drawing.Color.Red;
tiRemark.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add("项目备注说明:", tiRemark);
//项目备注
TableItemStyle tiRemarkContent = new TableItemStyle();
tiRemarkContent.Font.Size = 12;
tiRemarkContent.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add(mDOpenProject.NewProjectDescription, tiRemarkContent);
TableItemStyle tiCount = new TableItemStyle();
tiCount.Font.Size = 12;
tiCount.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add("共有" + count.ToString() + "条", tiCount);
if (data.Columns.Contains("ID"))
{
data.Columns.Remove("ID");
}
mx.DataTableToExcel(data);
}
#endregion
return View(list);
}
}
protected void btnSupplierExportExcel_Click(object sender, EventArgs e)
{
ExcelExport export = new ExcelExport("供应商产品" + DateTime.Now.ToString("yyyyMMdd-HHmmss"));
export.ExportProductExcel(SupplierProducts);
lblMsg.Text = "操作完成";
}
private void ExportToExcelFile()
{
var xlsExp = new ExcelExport<AgentMPIModel>(AgentMPIData);
xlsExp.ExportToFile(Product + "_agentmpi_" + StartDateValue.Replace("/", "") + "-" + EndDateValue.Replace("/", ""));
}
protected void btnExport_Click(object sender, EventArgs e)
{
ExcelExport export = new ExcelExport("产品导出_" + DateTime.Now.ToString("yyyyMMdd-HHmmss"));
export.ExportProductExcel(bizPC.GetDefaultCollection(GlobalVarible.GetUserId()).Products);
}
//
// GET: /BiddingExcute/
public ActionResult Detail(int? pageNo, string parm, FormCollection collection)
{
Dictionary<string, string> sitemaster = GetSiteMaster();
ViewData["SiteMaster"] = sitemaster;
Dictionary<string, string> dict = ParmHelper.Analysis(collection);
if (dict.Count != 0)
{
string parmeter = ParmHelper.BuildParm(dict);
return RedirectToAction("Detail", new { pageNo = 1, parm = parmeter });
}
if (pageNo == null)
{
return View();
}
int pageIndex = pageNo ?? 1;
int size = 20;//每页条数
Dictionary<string, string> use = ParmHelper.AnalysisParm(parm);
int count = 0;
IList<IDictionary> list = null;//分页数据
DataTable excelData = null;//excel数据
Dictionary<string, string> ht;//查询条件
if (use.ContainsKey("competeCommonName") || use.ContainsKey("competeFormulation") || use.ContainsKey("competeSpecification") || use.ContainsKey("competeQualityLevel"))
{
//得到本品和竞品列表
ht = use;
ht["start"] = (size * (pageIndex - 1) + 1).ToString(); //记录开始数
ht["end"] = (pageIndex * size).ToString(); //记录结束数
list = DBiddingMedicineDao.GetCompeteMedicine(ht, out count);
}
else
{
//得到本品列表
ht = new Dictionary<string, string>();
ht["productcode"] = use["productcode"];
ht["province"] = use["province"];
ht["start"] = (size * (pageIndex - 1) + 1).ToString(); //记录开始数
ht["end"] = (pageIndex * size).ToString(); //记录结束数
count = BiddingExcuteMapper.Get().QueryForObject<int>("DBiddingMedicine.GetMedicineCount", ht);
list = BiddingExcuteMapper.Get().QueryForList<IDictionary>("DBiddingMedicine.GetMedicine", ht);
}
//是否导出excel
if (use.ContainsKey("excel") && use["excel"] == "1")
{
if (use.ContainsKey("competeCommonName") || use.ContainsKey("competeFormulation") || use.ContainsKey("competeSpecification") || use.ContainsKey("competeQualityLevel"))
{
excelData = DBiddingMedicineDao.GetAllCompeteMedicine(ht, "top(1000)");
}
else
{
excelData = DBiddingMedicineDao.GetAllMedicine(ht);
}
ExcelExport mx = new ExcelExport();
mx.InitTh();
mx._fileName = "Emed-ZB-" + DateTime.Now.ToShortDateString() + ".xls";
if (excelData.Columns.Contains("OID"))
{
excelData.Columns.Remove("OID");
}
mx.DataTableToExcel(excelData);
}
ViewData["Province"] = BaseMapper.Get().QueryForObject<string>("CityCode.GetProvinceNameByValue", use["province"]);
ViewData["Count"] = count;
ViewData["Query"] = use;
//ViewData["Query"] = new BiddingQuery()
//{
// Province = use.ContainsKey("province") ? use["province"] : null,
// ProductEnterprise = use.ContainsKey("productEnterprise") ? use["productEnterprise"] : null,
// CommonName = use.ContainsKey("commonName") ? use["commonName"] : null,
// ChineseSpecification = use.ContainsKey("chinesespecification") ? use["chinesespecification"] : null,
// Formulation = use.ContainsKey("formulation") ? use["formulation"] : null,
// Excel = use.ContainsKey("excel") ? use["excel"] : null,
// CompeteCommonName = use.ContainsKey("competeCommonName") ? "checked=checked" : null,
// CompeteFormulation = use.ContainsKey("competeFormulation") ? "checked=checked" : null,
// CompeteSpecification = use.ContainsKey("competeSpecification") ? "checked=checked" : null,
// CompeteQualityLevel = use.ContainsKey("competeQualityLevel") ? "checked=checked" : null
//};
PagedList<IDictionary> model = new PagedList<IDictionary>(list, pageIndex, size, count);
//ViewData["Start"] = use["start"];
return View(model);
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
ExcelExport export = new ExcelExport("产品资料" + DateTime.Now.ToString("yyyyMMdd-HHmmss"));
export.ExportProductExcel(ProductsWithEnglish);
}
public ActionResult Detail(int id, int? pageNo, string parm, OpenBidProjectQuery query)
{
Dictionary<string, string> sitemaster = GetSiteMaster();
ViewData["SiteMaster"] = sitemaster;
if (sitemaster["istest"] == "1")
{
int uid = Int32.Parse(sitemaster["userid"]);
IList<int> openprojectlist = CRMMapper.Get().QueryForList<int>("OpenBidNumForTest.myselectopen", uid);
//先检查一共看的数目
if (openprojectlist.Count > 0)
{
//如果有看得记录，先检查是否是看过的
Hashtable htt = new Hashtable();
htt["pid"] = id;
htt["uid"] = uid;
IList<int> sawid = CRMMapper.Get().QueryForList<int>("OpenBidNumForTest.myselectopenpid", htt);
//如果没看过，就检查是否超过最大值
if (sawid.Count == 0)
{
if (openprojectlist.Count < 5)
{
//没超过就写入记录
OpenBidNumForTest ob = new OpenBidNumForTest();
ob.OpenprojectNum = id;
ob.UserID = uid;
new OpenBidNumForTestDao().Insert(ob);
}
else
{
//超过了
return RedirectToAction("List");
}
}
}
else
{
//之前没有看过也写入记录
OpenBidNumForTest ob = new OpenBidNumForTest();
ob.OpenprojectNum = id;
ob.UserID = uid;
new OpenBidNumForTestDao().Insert(ob);
}
}
DOpenProjectDao mDOpenProjectDao = new DOpenProjectDao();
DOpenProject mDOpenProject = mDOpenProjectDao.Find(id);
int interval = 0;
if (EnterPriseMemberInfo.Memberlevel == 201)
interval = 6;
if (EnterPriseMemberInfo.Memberlevel == 301)
interval = 12;
DateTime limitedate = DateTime.Now.AddMonths(interval);
if ((EnterPriseMemberInfo.Memberlevel == 201 || EnterPriseMemberInfo.Memberlevel == 301) && mDOpenProject.OpenDate > limitedate)
{
return RedirectToAction("MemberLevelError", "Base");
}
ViewData["ProjectDescription"] = mDOpenProject.NewProjectDescription;
ViewData["Summary"] = mDOpenProject.Summary;
ViewData["OID"] = mDOpenProject.OID;
//如果有参数
if (query.IsHaveProperty())
{
parm = query.GetParameter();
ViewData["Query"] = query;
return RedirectToAction("Detail", new { id = id, pageNo = 1, parm = parm });
}
else
{
int PageNo = pageNo ?? 1;
//表明第一次进来
if (query.CommonName != null)
{
query.LoadProperties(parm);
}
if (string.IsNullOrEmpty(query.CommonName) && string.IsNullOrEmpty(query.ProductEnterpriseName) && string.IsNullOrEmpty(query.TenderEnterpriseName) && query.CommonName.Length < 3)
{
pageNo = 1;
}
else
{
query.IsSearched = "1";
}
//页码与记录数
int count = 0;
//是否竞品条件
bool compete = false;
//取数据与记录数
List<string> th = new List<string>();
DOpenMedicineDao m = new DOpenMedicineDao();
DataTable data = m.GetData(id, PageNo, PageSizeDetail, query, out count, ref compete,ref th);
#region 数据处理
if (data == null)
{
data = new DataTable();
}
if (!data.Columns.Contains("ID"))
{
DataColumn dc = new DataColumn();
dc.ColumnName = "ID";
data.Columns.Add(dc);
}
int beginNo = 0;
if (!compete)
{
beginNo = (PageNo - 1) * PageSizeDetail;
}
for (int i = 1; i<= data.Rows.Count; i++)
{
data.Rows[i-1]["ID"] =(beginNo+ i).ToString();
}
if (data.Columns.Contains("ROWID"))
{
data.Columns.Remove("ROWID");
}
if (data.Columns.Contains("OID"))
{
data.Columns.Remove("OID");
}
List<DataRow> listRow = new List<DataRow>();
foreach (DataRow dr in data.Rows)
{
listRow.Add(dr);
}
PagedList<DataRow> list;
if (compete)
{
list = new PagedList<DataRow>(listRow, PageNo, PageSizeDetail);
}
else
{
list = new PagedList<DataRow>(listRow, PageNo, PageSizeDetail, count);
}
ViewData["Compete"] = compete;
ViewData["Query"] = query;
ViewData["Count"] = count;
ViewData["Th"] = th;
#endregion
#region excel
if (sitemaster["istest"] == "0" && query.Excel == "1")
{
ExcelExport mx = new ExcelExport();
mx.InitTh();
if (query.CommonName != null)
mx._fileName = query.CommonName + DateTime.Now.ToShortDateString() + ".xls";
else
{
if (query.ProductEnterpriseName != null)
mx._fileName = query.ProductEnterpriseName + DateTime.Now.ToShortDateString() + ".xls";
else
mx._fileName = query.TenderEnterpriseName + DateTime.Now.ToShortDateString() + ".xls";
}
//项目名称
TableItemStyle tiName = new TableItemStyle();
tiName.Font.Size = 14;
tiName.ForeColor = System.Drawing.Color.Red;
tiName.HorizontalAlign = HorizontalAlign.Center;
mx.HeadExtrInfo.Add(mDOpenProject.NewProjectName, tiName);
//项目备注
TableItemStyle tiRemark = new TableItemStyle();
tiRemark.Font.Size = 13;
tiRemark.ForeColor = System.Drawing.Color.Red;
tiRemark.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add("项目备注说明:", tiRemark);
//项目备注
TableItemStyle tiRemarkContent = new TableItemStyle();
tiRemarkContent.Font.Size = 12;
tiRemarkContent.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add(mDOpenProject.NewProjectDescription, tiRemarkContent);
TableItemStyle tiCount = new TableItemStyle();
tiCount.Font.Size = 12;
tiCount.HorizontalAlign = HorizontalAlign.Left;
mx.HeadExtrInfo.Add("共有" + count.ToString() + "条", tiCount);
if (data.Columns.Contains("ID"))
{
data.Columns.Remove("ID");
}
//20111019修改按显示表头导出,去掉多余列
if (data.Columns.Contains("Mine"))
{
data.Columns.Remove("Mine");
}
mx._thName = th.ToArray();//20111019修改按显示表头导出
mx.DataTableToExcel(data);
}
#endregion
return View(list);
}
}
private void SalesmanReport()
{
if (SelectedSalesman == null) return;
SaveFileDialog saveFileDialog = new SaveFileDialog { Filter = "Excel files (*.xlsx)|*.xlsx", InitialDirectory = Directory.GetCurrentDirectory()};
saveFileDialog.ShowDialog();
if (!saveFileDialog.FileName.EndsWith(".xlsx"))
{
//MessageBox.Show("Файл для сохранения не выбран", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
ExcelExport export = new ExcelExport();
export.SalesmanReport(saveFileDialog.FileName, SelectedSalesman.ID, FromDate, ThroughDate, _context);
MessageBox.Show("Отчет готов");
}
