public async Task <IActionResult> Get(IFormFile formFile) { DataTable dt = new DataTable(); var ms = new MemoryStream(); formFile.CopyTo(ms); using (Stream inputStream = new MemoryStream(ms.ToArray())) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; dt = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); } } string xml = @"<?xml version=""1.0"" encoding=""utf-8"" ?><resources>"; for (int i = 0; i < dt.Rows.Count; i++) { xml += @"<string name=""" + dt.Rows[i][0]; xml += @""">" + dt.Rows[i][1] + "</string>"; } xml += @"</resources>"; return(Ok(xml)); }
private void GetWorksheetPipeSilt(IWorksheet worksheet, int targetId) { var data = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues) .AsEnumerable(); if (data == null) { return; } var dt1 = _dw1.DataTableToClasses <PipeSilt>(data); string commond = ""; string nowDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); foreach (var row1 in dt1) { row1.targetId = targetId; string existStr = IsExistOfPiNum(targetId, row1.P_NO, 0); if (existStr == "") { row1.MEMO += (string.IsNullOrEmpty(row1.MEMO)) ? "找不到管線" : ",找不到管線"; logPiNo.Add(row1.P_NO); } else { row1.P_NO = existStr; } commond += _dw1.ClassToSqlInserCommand(row1, nowDate) + "\n"; } OleDbCommand command = new OleDbCommand(commond); _dw1.InsertData(command); }
internal void OnImportClicked(object sender, EventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; //Initializing Workbook Assembly assembly = typeof(App).GetTypeInfo().Assembly; Stream fileStream = null; #if COMMONSB fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.ExportSales.xlsx"); #else fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.ExportSales.xlsx"); #endif IWorkbook workbook = application.Workbooks.Open(fileStream); workbook.Version = ExcelVersion.Excel2013; IWorksheet sheet = workbook.Worksheets[0]; //Export SheetData as DataTable DataTable dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Convert DataTable to ObservableCollection ObservableCollection <CustomerObject> data = new ObservableCollection <CustomerObject>(); UpdateGridData(data, dataTable); //Set DataSource to Grid dataGrid.ItemsSource = data; this.btnGenerate.IsEnabled = true; workbook.Close(); excelEngine.Dispose(); }
private void Button_Click(object sender, RoutedEventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //Import the data to worksheet. IList <OrderInfo> reports = viewModel.Generate(); worksheet.ImportData(reports, 1, 1, true); // Read data from the worksheet and Export to the DataTable. DataTable customersTable = worksheet.ExportDataTable(1, 1, 15, 5, ExcelExportDataTableOptions.ColumnNames); this.dataGrid.ItemsSource = customersTable; workbook.Close(); excelEngine.Dispose(); }
public FileStreamResult GeneratePPT(List <ViewData> viewDataList) { //Creates a PowerPoint instance IPresentation pptxDoc = Presentation.Create(); ExcelEngine excelEngine = new ExcelEngine(); IApplication excelApplication = excelEngine.Excel; excelApplication.DefaultVersion = ExcelVersion.Excel2016; foreach (var item in viewDataList) { if (String.IsNullOrEmpty(item.ChartType)) { continue; } IWorkbook wb = excelApplication.Workbooks.Open(item.DataStream, ExcelOpenType.CSV); IWorksheet worksheet = wb.Worksheets[0]; worksheet.Name = item.Name; DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.DetectColumnTypes); if (dataTable == null) { continue; } wb.Close(); ISlide slide = pptxDoc.Slides.Add(SlideLayoutType.Blank); //Handle error here to continue after chart testing is done. try { if (item.ChartType.Equals("Table")) { CreateTable(dataTable, slide, item); } else { CreateChart(slide, dataTable, item); } } catch (Exception e) { _logger.LogError(e.Message); continue; } } MemoryStream stream = new MemoryStream(); pptxDoc.Save(stream); //Set the position as '0'. stream.Position = 0; //Download the PowerPoint file in the browser FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/powerpoint"); fileStreamResult.FileDownloadName = "Editable-" + DateTime.Now.ToString() + ".pptx"; //WorkbookTuple.Item2.Close(); pptxDoc.Close(); return(fileStreamResult); }
private void btnImport_Click(object sender, System.EventArgs e) { //Initialize the Excel Engine using (ExcelEngine excelEngine = new ExcelEngine()) { //Initialize Application IApplication application = excelEngine.Excel; //Set default version for application. application.DefaultVersion = ExcelVersion.Excel2013; //Open existing workbook with data entered Assembly assembly = typeof(Form1).GetTypeInfo().Assembly; Stream fileStream = assembly.GetManifestResourceStream("ExceltoDataGrid.Sample.xlsx"); IWorkbook workbook = application.Workbooks.Open(fileStream); //Accessing first worksheet in the workbook IWorksheet worksheet = workbook.Worksheets[0]; //Export data from Excel worksheet to DataTable DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Load the data to DataGridView dataGridView.DataSource = customersTable; //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; } }
private void button1_Click(object sender, EventArgs e) { eng = new ExcelEngine(); #if NETCORE workBook = eng.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\common\Data\XlsIO\Input.xls"); #else workBook = eng.Excel.Workbooks.Open(@"..\..\..\..\..\..\common\Data\XlsIO\Input.xls"); #endif IWorksheet sheet = workBook.Worksheets[0]; IWorksheet sheet2 = workBook.Worksheets[1]; //Enable to calculate formulas in the sheet. sheet.EnableSheetCalculations(); //Assign the sheet calcEngine. calcEngine = sheet.CalcEngine; //Load the datagrids with the data from Xls file. DataTable dt = new DataTable("Input Data"); dt = sheet.ExportDataTable(1, 1, 15, 5, ExcelExportDataTableOptions.None); this.dataGridView1.DataSource = dt; this.dataGridView1.Rows[0].Cells[0].Selected = false; DataTable dt2 = new DataTable("Sheet2 Data"); dt2 = sheet2.ExportDataTable(1, 1, 15, 8, ExcelExportDataTableOptions.None); this.dataGridView2.DataSource = dt2; this.dataGridView2.Rows[0].Cells[0].Selected = false; this.button1.Enabled = false; for (int i = 0; i < 5; i++) { switch (i) { case 3: this.dataGridView1.Columns[i].Width = 180; break; case 4: this.dataGridView1.Columns[i].Width = 210; break; default: this.dataGridView1.Columns[i].Width = 60; break; } this.dataGridView1.Columns[i].HeaderText = string.Format("{0}", (char)((int)'A' + i)); } for (int j = 0; j < 8; j++) { this.dataGridView2.Columns[j].Width = 70; this.dataGridView2.Columns[j].HeaderText = string.Format("{0}", (char)((int)'A' + j)); } this.dataGridView1.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle1; this.dataGridView1.RowHeadersDefaultCellStyle = dataGridViewCellStyle1; this.dataGridView1.EnableHeadersVisualStyles = false; this.dataGridView2.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle1; this.dataGridView2.RowHeadersDefaultCellStyle = dataGridViewCellStyle1; this.dataGridView2.EnableHeadersVisualStyles = false; }
private void toolStripButton1_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); if (openFileDialog1.FileName != "") { //Imports Data //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //Open an existing xls IWorkbook workbook = application.Workbooks.Open(openFileDialog1.FileName); IWorksheet sheet1 = workbook.Worksheets[0]; IWorksheet sheet2 = workbook.Worksheets[1]; //Read data DataTable customersTable1 = sheet1.ExportDataTable(sheet1.UsedRange, ExcelExportDataTableOptions.ColumnNames); DataTable customersTable2 = sheet2.ExportDataTable(sheet2.UsedRange, ExcelExportDataTableOptions.ColumnNames); weighted_distanceDatagridForm.dataGridView1.DataSource = customersTable1; demandDatagridForm.dataGridView1.DataSource = customersTable2; //Show Datagrid weighted_distanceDatagridForm.Show(); demandDatagridForm.Show(); //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); if (toolStripComboBox2.Text == (string)toolStripComboBox2.Items[0]) { Parameter.m = demandDatagridForm.dataGridView1.RowCount - 1; Parameter.n = Parameter.m; for (int i = 0; i < demandDatagridForm.dataGridView1.RowCount - 1; i++) { Parameter.demand[i] = double.Parse(demandDatagridForm.dataGridView1[1, i].Value.ToString()); for (int j = 0; j < weighted_distanceDatagridForm.dataGridView1.RowCount - 1; j++) { Parameter.weighted_distance[int.Parse(weighted_distanceDatagridForm.dataGridView1[0, j].Value.ToString()) - 1, int.Parse(weighted_distanceDatagridForm.dataGridView1[1, j].Value.ToString()) - 1] = double.Parse(weighted_distanceDatagridForm.dataGridView1[2, j].Value.ToString()); Parameter.weighted_distance[int.Parse(weighted_distanceDatagridForm.dataGridView1[1, j].Value.ToString()) - 1, int.Parse(weighted_distanceDatagridForm.dataGridView1[0, j].Value.ToString()) - 1] = double.Parse(weighted_distanceDatagridForm.dataGridView1[2, j].Value.ToString()); } } for (int i = 0; i < Parameter.n; i++) { for (int j = 0; j < Parameter.m; j++) { Parameter.weighted_distance[i, j] = Parameter.weighted_distance[i, j] * Parameter.demand[i]; } } } else if (toolStripComboBox2.Text == (string)toolStripComboBox2.Items[1]) { Parameter.m = weighted_distanceDatagridForm.dataGridView1.RowCount - 1; Parameter.n = weighted_distanceDatagridForm.dataGridView1.ColumnCount - 1; } weighted_distanceDatagridForm.Show(); demandDatagridForm.Show(); } }
public void CSVToMarkingData(string path) { using (Stream inputStream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; markingData = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); markingDataColumns = markingData.Columns.Cast <DataColumn>().ToList(); markingDataColumnHeadings = markingDataColumns.Cast <DataColumn>() .Select(x => x.ColumnName) .ToList(); } } /* * Microsoft.Office.Interop.Excel.Application objXL = null; * Microsoft.Office.Interop.Excel.Workbook objWB = null; * objXL = new Microsoft.Office.Interop.Excel.Application(); * objWB = objXL.Workbooks.Open(path); * Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1]; * * int rows = objSHT.UsedRange.Rows.Count; * int cols = objSHT.UsedRange.Columns.Count; * DataTable dt = new DataTable(); * int noofrow = 1; * * for (int c = 1; c <= cols; c++) * { * string colname = objSHT.Cells[1, c].Text; * dt.Columns.Add(colname); * noofrow = 2; * } * * for (int r = noofrow; r <= rows; r++) * { * DataRow dr = dt.NewRow(); * for (int c = 1; c <= cols; c++) * { * dr[c - 1] = objSHT.Cells[r, c].Text; * } * * dt.Rows.Add(dr); * } * objWB.Close(); * objXL.Quit(); * markingData = dt; * markingDataColumns = markingData.Columns.Cast<DataColumn>().ToList(); * markingDataColumnHeadings = markingDataColumns.Cast<DataColumn>() * .Select(x => x.ColumnName) * .ToList(); */ }
/// <summary> /// Export the spreadsheet data in GridGroupingControl. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void exportbtn_Click_1(object sender, EventArgs e) { if (spreadsheet != null) { IWorksheet sheet = spreadsheet.Workbook.Worksheets[0]; IRange range = sheet.Range["A1:K50"]; DataTable Dt = sheet.ExportDataTable(range, ExcelExportDataTableOptions.ColumnNames); GridView dgv = new GridView(); dgv.gridGroupingControl.DataSource = Dt; dgv.ShowDialog(); } }
public static System.Data.DataTable ConvertExcelToDataTable(string FileName) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Open(FileName); IWorksheet worksheet = workbook.Worksheets[0]; System.Data.DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); return(customersTable); } }
public async Task <IActionResult> CreatePackagesFromExcel(IFormFile file) { var path = Path.Combine(Path.GetTempPath(), file.FileName); using (var stream = System.IO.File.Create(path)) { await file.CopyToAsync(stream); } DataTable dataTable = null; using (Stream inputStream = System.IO.File.OpenRead(path)) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; dataTable = worksheet.ExportDataTable( worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); } } List <Package> packagesToCreate = new List <Package>(); for (var i = 0; i < dataTable.Rows.Count; i++) { packagesToCreate.Add( Package.CreateNew( size: (PackageSize)Convert.ToInt32(dataTable.Rows[i]["Package_Size"]), weight: Convert.ToInt32(dataTable.Rows[i]["Package_Weight"]), isFragile: Convert.ToBoolean(dataTable.Rows[i]["Package_IsFragile"]), valueToPay: Convert.ToDecimal(dataTable.Rows[i]["Package_ValueToPay"]), countryCode: dataTable.Rows[i]["Package_CountryCode"].ToString(), countryISOCode: GetCountryCodeDetails(dataTable.Rows[i]["Package_CountryCode"].ToString()), areaCode: dataTable.Rows[i]["Package_AreaCode"].ToString(), shipment: Shipment.CreateNew( checkPoint: CheckPoint.CreateNew( city: dataTable.Rows[i]["CheckPoint_City"].ToString(), country: dataTable.Rows[i]["CheckPoint_Country"].ToString(), controlType: (ControlType)Convert.ToInt32(dataTable.Rows[i]["CheckPoint_ControlType"]), placeType: (PlaceType)Convert.ToInt32(dataTable.Rows[i]["CheckPoint_PlaceType"]))))); } await _packageRepository.CreatePackages(packagesToCreate); return(Ok()); }
private static void OnExecuteExportToDataTable(object sender, ExecutedRoutedEventArgs args) { SfSpreadsheet Spreadsheet = args.Source as SfSpreadsheet; if (Spreadsheet != null) { IWorksheet sheet = Spreadsheet.Workbook.Worksheets[0]; IRange range = sheet.Range["A1:K50"]; DataTable Dt = sheet.ExportDataTable(range, ExcelExportDataTableOptions.ColumnNames); DataGridView dgv = new DataGridView(); dgv.DataContext = Dt; dgv.ShowDialog(); } }
private void btnImport_Click(object sender, System.EventArgs e) { //Imports Data from the Template spreadsheet into the Grid. #region Workbook Initialize //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //Get path of the Input file string inputPath = GetTemplatePath("NorthwindDataTemplate.xls"); //Open an existing spreadsheet which will be used as a template for generating the new spreadsheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. IWorkbook workbook = application.Workbooks.Open(inputPath); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion worksheet.ExportDataTableEvent += ExportDataTableEventActions; #region Import Data From Excel to DataGrid //Read data from spreadsheet. DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); this.dataGridView1.DataSource = customersTable; if (dataGridView1.Columns.Count > 0) { this.dataGridView1.Columns[1].Width = 145; this.dataGridView1.Columns[2].Width = 100; this.dataGridView1.Columns[3].Width = 118; this.dataGridView1.Columns[4].Width = 142; this.dataGridView1.Columns[5].Width = 72; } this.dataGridView1.CurrentCell.Selected = false; btnExport.Enabled = true; #endregion #region Workbook Close and Dispose //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); #endregion }
private void exctodt(string nombre) { ExcelEngine excelEngine = new ExcelEngine(); excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; IApplication application = excelEngine.Excel; //string documento = @"C:\Users\Abril\Desktop\Precios ejemplo.xlsx"; IWorkbook workbook = application.Workbooks.Open(nombre); IWorksheet sheet = workbook.Worksheets[0]; try { if (sheet.Range["A1"].Text == "Codigo" && sheet.Range["B1"].Text == "PL") { customersTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.DetectColumnTypes); //if (customersTable.Columns[0].) //{ //} customersTable.Columns.Add("01", Type.GetType("System.String"), "(PL * 0.95)"); customersTable.Columns.Add("02", Type.GetType("System.String"), "(PL * 0.85)"); customersTable.Columns.Add("03", Type.GetType("System.String"), "(PL * 0.95)"); customersTable.Columns.Add("04", Type.GetType("System.String"), "(PL * 0.85)"); customersTable.Columns.Add("05", Type.GetType("System.String"), "(PL * 0.95)"); customersTable.Columns.Add("06", Type.GetType("System.String"), "(PL * 0.85)"); customersTable.Columns.Add("07", Type.GetType("System.String"), "(PL * 0.95)"); customersTable.Columns.Add("08", Type.GetType("System.String"), "(PL * 0.85)"); customersTable.Columns.Add("09", Type.GetType("System.String"), "(PL * 0.95)"); customersTable.Columns.Add("10", Type.GetType("System.String"), "(PL * 0.85)"); customersTable.Columns.Add("11", Type.GetType("System.String"), "(PL * 0.95)"); customersTable.Columns.Add("12", Type.GetType("System.String"), "(PL * 0.85)"); dataGrid.ItemsSource = customersTable.DefaultView; workbook.Close(); } else { MessageBox.Show("Nombre de columnas invalidas "); } } catch (Exception e) { MessageBox.Show("El formato del documento es invalido "); } }
public static System.Data.DataTable ConvertExcelToDataTable(string FileName) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Open(FileName); IWorksheet worksheet = workbook.Worksheets[0]; //Read data from the worksheet and Export to the DataTable System.Data.DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Binding exported DataTable to data grid, likewise it can binded to any //user interface control which supports binding return(customersTable); } }
static void Main(string[] args) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Xlsx; FileStream inputStream = new FileStream("../../Data/Sample.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; //Read data from the worksheet and exports to the data table DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Saving the workbook as stream FileStream stream = new FileStream("ExportToDT.xlsx", FileMode.Create, FileAccess.ReadWrite); workbook.SaveAs(stream); stream.Dispose(); } }
private void ImportExcelFile() { try { string strFileName = ""; OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "Excel Files|*.xls;*.xlsx"; // openFileDialog.DefaultExt = ".xls"; if (openFileDialog.ShowDialog() == DialogResult.OK) { dgPiutang.DataSource = null; strFileName = openFileDialog.FileName; txtFileName.Text = strFileName; #region Get Correct Worksheet in excel file DateTime dtStart = DateTime.Now; ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(strFileName); IWorksheet sheet = workbook.Worksheets[0]; #endregion dt.Clear(); dt = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); dgPiutang.DataSource = dt; dgPiutang.AutoSizeColumnsMode = AutoSizeColumnsMode.AllCells; } } catch (Exception err) { MsgBoxUtil.MsgError(err.Message.ToString()); } }
private void GetWorksheetRd(IWorksheet worksheet, int targetId) { var data = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues) .AsEnumerable(); if (data == null) { return; } var dt1 = _dw1.DataTableToClasses <RainwaterDitch>(data); string commond = ""; string nowDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); foreach (var row1 in dt1) { row1.targetId = targetId; commond += _dw1.ClassToSqlInserCommand(row1, nowDate) + "\n"; } OleDbCommand command = new OleDbCommand(commond); _dw1.InsertData(command); }
static void Main(string[] args) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Xlsx; FileStream inputStream = new FileStream("../../../Data/Sample.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet worksheet = workbook.Worksheets[0]; //Event to choose an action while exporting data from Excel to data table. worksheet.ExportDataTableEvent += ExportDataTable_EventAction; //Read data from the worksheet and exports to the data table //The event will be triggered at this line while exporting data from each cell in the Excel worksheet. DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Saving the workbook as stream FileStream stream = new FileStream("ExportToDT.xlsx", FileMode.Create, FileAccess.ReadWrite); workbook.SaveAs(stream); stream.Dispose(); } }
void ButtonImportClicked(object sender, EventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; string resourcePath = ""; resourcePath = "SampleBrowser.Samples.XlsIO.Template.ExportSales.xlsx"; Assembly assembly = Assembly.GetExecutingAssembly(); Stream fileStream = assembly.GetManifestResourceStream(resourcePath); IWorkbook workbook = application.Workbooks.Open(fileStream); IWorksheet sheet = workbook.Worksheets[0]; //Export DataTable from worksheet System.Data.DataTable dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Set exported DataTable as source to DataGrid. sfGrid.ItemsSource = dataTable; btnExport.Enabled = true; }
/// <summary> /// 将Excel文件里数据读取为DataTable /// </summary> /// <param name="filePath">Excel文件路径</param> public static DataTable ExportExcelDataTable(string filePath) { if (string.IsNullOrWhiteSpace(filePath)) { throw new ArgumentNullException("filePath"); } if (!File.Exists(filePath)) { throw new ArgumentException("{0} 文件不存在".FormatWith(filePath)); } var excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(filePath, ExcelOpenType.Automatic); IWorksheet sheet = workbook.Worksheets[0]; DataTable dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); workbook.Close(); excelEngine.Dispose(); return(dataTable); }
/// <summary> /// Convert the Excel document to JSON /// </summary> /// <returns>Return the JSON document as stream</returns> public DataTable ImportXlsIO(string button, string option, bool isSchema) { //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. importOptions = option; //Step 1 : Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { //Step 2 : Instantiate the excel application object IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //Opening the encrypted Workbook FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-data-template.xls"), FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; worksheet.ExportDataTableEvent += ExportDataTableEventActions; //Read data from spreadsheet. DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); return(customersTable); } }
public ActionResult ImportExportDataTable(string saveOption, string button, string importOption) { string fileName = "NorthwindDataTemplate.xls"; ViewBag.exportButtonState = "disabled=\"disabled\""; ///SaveOption Null if (saveOption == null || button == null) { ViewBag.DataSource = null; return(View()); } //Start Business Object Functions if (button == "Input Template") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(fileName)); return(excelEngine.SaveAsActionResult(workbook, fileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else if (button == "Import From Excel") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(fileName)); IWorksheet sheet = workbook.Worksheets[0]; if (importOption == "Skip") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventSkip; ViewBag.importOptionSkip = "value=" + importOption + " checked = \"checked\""; } else if (importOption == "Replace") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventReplace; ViewBag.importOptionReplace = "value=" + importOption + " checked = \"checked\""; } else { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventStop; ViewBag.importOptionStop = "value=" + importOption + " checked = \"checked\""; } dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Close the workbook. workbook.Close(); excelEngine.Dispose(); ViewBag.DataSource = dataTable; ViewBag.exportButtonState = ""; return(View()); } else { //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; if (saveOption == "Xls") { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2016; } //Open an existing spreadsheet which will be used as a template for generating the new spreadsheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. IWorkbook workbook; workbook = excelEngine.Excel.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; //Import data table to worksheet sheet.ImportDataTable(dataTable, true, 1, 1, true); sheet.UsedRange.AutofitColumns(); try { //Saving the workbook to disk. This spreadsheet is the result of opening and modifying //an existing spreadsheet and then saving the result to a new workbook. if (saveOption == "Xlsx") { return(excelEngine.SaveAsActionResult(workbook, "ExportDataTable.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } else { return(excelEngine.SaveAsActionResult(workbook, "ExportDataTable.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); } return(View()); }
public async Task <IActionResult> Save() { string filePath = "App_Data/TempData/"; string directoryPath = Path.Combine(new FileInfo(filePath).Directory.FullName); if (!Directory.Exists(directoryPath)) { Directory.CreateDirectory(directoryPath); } try { if (HttpContext.Request.Form.Files.Count > 0) { for (int i = 0; i < HttpContext.Request.Form.Files.Count; ++i) { IFormFile httpPostedFile = HttpContext.Request.Form.Files[i]; if (httpPostedFile != null) { filePath = Path.Combine(directoryPath, httpPostedFile.FileName); if (!System.IO.File.Exists(filePath)) { using (var fileStream = new FileStream(filePath, FileMode.Create)) { await httpPostedFile.CopyToAsync(fileStream); ExcelEngine excelEngine = new ExcelEngine(); //Loads or open an existing workbook through Open method of IWorkbooks fileStream.Position = 0; IWorkbook workbook = excelEngine.Excel.Workbooks.Open(httpPostedFile.OpenReadStream()); IWorksheet worksheet = workbook.Worksheets[0]; // Read data from the worksheet and Export to the DataTable. DataTable table = worksheet.ExportDataTable(worksheet.UsedRange.Row, worksheet.UsedRange.Column, worksheet.UsedRange.LastRow, worksheet.UsedRange.LastColumn, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues); string JSONString = string.Empty; JSONString = JsonConvert.SerializeObject(table); ViewBag.data = JsonConvert.SerializeObject(table, Formatting.Indented, new JsonSerializerSettings { Converters = new[] { new Newtonsoft.Json.Converters.DataTableConverter() } }); //return View(); } return(Ok(ViewBag.data)); } else { return(BadRequest("File already exists")); } } } } return(BadRequest("No file in request"));; } catch (Exception e) { return(BadRequest(e.Message)); } }
private void button1_Click(object sender, EventArgs e) { eng = new ExcelEngine(); #if NETCORE workBook = eng.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\..\Common\Data\XlsIO\Input.xls"); #else workBook = eng.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\Input.xls"); #endif IWorksheet sheet = workBook.Worksheets[0]; IWorksheet sheet2 = workBook.Worksheets[1]; //Enable to calculate formulas in the sheet. sheet.EnableSheetCalculations(); //Assign the sheet calcEngine. calcEngine = sheet.CalcEngine; //Load the datagrids with the data from Xls file. DataTable dt = new DataTable("Input Data"); dt = sheet.ExportDataTable(1, 1, 15, 5, ExcelExportDataTableOptions.None); this.dataGrid1.DataSource = dt; DataTable dt2 = new DataTable("Sheet2 Data"); dt2 = sheet2.ExportDataTable(1, 1, 15, 8, ExcelExportDataTableOptions.None); this.dataGrid2.DataSource = dt2; this.button1.Enabled = false; DataGridTableStyle tabStyle = new DataGridTableStyle(); tabStyle.MappingName = dt.TableName; this.dataGrid1.TableStyles.Add(tabStyle); DataGridTableStyle tabStyle2 = new DataGridTableStyle(); tabStyle2.MappingName = dt2.TableName; this.dataGrid2.TableStyles.Add(tabStyle2); for (int i = 0; i < 5; i++) { switch (i) { case 3: tabStyle.GridColumnStyles[i].Width = 180; break; case 4: tabStyle.GridColumnStyles[i].Width = 210; break; default: tabStyle.GridColumnStyles[i].Width = 60; break; } tabStyle.GridColumnStyles[i].HeaderText = string.Format("{0}", (char)((int)'A' + i)); tabStyle.GridColumnStyles[i].Alignment = HorizontalAlignment.Left; } for (int j = 0; j < 8; j++) { tabStyle2.GridColumnStyles[j].Width = 70; tabStyle2.GridColumnStyles[j].HeaderText = string.Format("{0}", (char)((int)'A' + j)); tabStyle2.GridColumnStyles[j].Alignment = HorizontalAlignment.Left; } tabStyle.HeaderBackColor = Color.LightSteelBlue; tabStyle2.HeaderBackColor = Color.LightSteelBlue; }
//For Session //public HttpSessionStateBase Session { get; } public ActionResult DataTable(string saveOption, string button) { string basePath = _hostingEnvironment.WebRootPath; ViewBag.exportButtonState = "disabled=\"disabled\""; ///SaveOption Null if (saveOption == null || button == null) { return(View()); } //Start Data Table Functions if (button == "Input Template") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read); // Opening the Existing Worksheet from a Workbook. IWorkbook workbook = application.Workbooks.Open(inputStream); try { string ContentType = null; string fileName = null; if (saveOption == "Xls") { workbook.Version = ExcelVersion.Excel97to2003; ContentType = "Application/vnd.ms-excel"; fileName = "ExportSales.xls"; } else { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "ExportSales.xlsx"; } MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, ContentType, fileName)); } catch (Exception) { } } else if (button == "Import From Excel") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); IWorksheet sheet = workbook.Worksheets[0]; //Export Data Table DataTable customersTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Add Customer Unique ID DataColumn column = customersTable.Columns.Add("ID"); column.SetOrdinal(0); for (int i = 0; i < customersTable.Rows.Count; i++) { customersTable.Rows[i][0] = i + 1; } //Close the workbook. workbook.Close(); excelEngine.Dispose(); //Set the grid value to the Session _sales = customersTable; ViewBag.DataSource = _sales; ViewBag.exportButtonState = ""; return(View()); } else { //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; if (saveOption == "Xls") { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2016; } //Open an existing spreadsheet which will be used as a template for generating the new spreadsheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. IWorkbook workbook; workbook = excelEngine.Excel.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; //Import DataTable to worksheet sheet.ImportDataTable(_sales, false, 5, 1); sheet.Range["E4"].Text = ""; #region Define Styles IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Font.RGBColor = Color.FromArgb(0, 83, 141, 213); pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Font.Color = ExcelKnownColors.White; tableHeader.Font.Bold = true; tableHeader.Font.Size = 11; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Color = Color.FromArgb(0, 118, 147, 60); tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; #endregion #region Apply Styles // Apply style for header sheet["A1:E1"].Merge(); sheet["A1"].Text = "Yearly Sales Report"; sheet["A1"].CellStyle = pageHeader; sheet["A2:E2"].Merge(); sheet["A2"].Text = "Namewise Sales Comparison Report"; sheet["A2"].CellStyle = pageHeader; sheet["A2"].CellStyle.Font.Bold = false; sheet["A2"].CellStyle.Font.Size = 16; sheet["A3:A4"].Merge(); sheet["B3:B4"].Merge(); sheet["E3:E4"].Merge(); sheet["C3:D3"].Merge(); sheet["C3"].Text = "Sales"; sheet["A3:E4"].CellStyle = tableHeader; sheet["A3"].Text = "S.ID"; sheet["B3"].Text = "Sales Person"; sheet["C4"].Text = "January - June"; sheet["D4"].Text = "July - December"; sheet["E3"].Text = "Change(%)"; sheet.UsedRange.AutofitColumns(); sheet.Columns[0].ColumnWidth = 10; sheet.Columns[1].ColumnWidth = 24; sheet.Columns[2].ColumnWidth = 21; sheet.Columns[3].ColumnWidth = 21; sheet.Columns[4].ColumnWidth = 16; #endregion try { string ContentType = null; string fileName = null; if (saveOption == "Xls") { workbook.Version = ExcelVersion.Excel97to2003; ContentType = "Application/vnd.ms-excel"; fileName = "ExportDataTable.xls"; } else { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "ExportDataTable.xlsx"; } MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, ContentType, fileName)); } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); } return(View()); }
public ActionResult ImportExportDataTable(string saveOption, string button, string importOption) { string basePath = _hostingEnvironment.WebRootPath; ViewBag.exportButtonState = "disabled=\"disabled\""; ///SaveOption Null if (saveOption == null || button == null) { ViewBag.DataSource = null; return(View()); } //Start Business Object Functions if (button == "Input Template") { Stream ms = new FileStream(basePath + @"/XlsIO/NorthwindDataTemplate.xls", FileMode.Open, FileAccess.Read); string ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; string fileName = "NorthwindDataTemplate.xls"; return(File(ms, ContentType, fileName)); } else if (button == "Import From Excel") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; Stream sampleFile = new FileStream(basePath + @"/XlsIO/NorthwindDataTemplate.xls", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(sampleFile); IWorksheet sheet = workbook.Worksheets[0]; if (importOption == "Skip") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventSkip; ViewBag.importOptionSkip = "value=" + importOption + " checked = \"checked\""; } else if (importOption == "Replace") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventReplace; ViewBag.importOptionReplace = "value=" + importOption + " checked = \"checked\""; } else { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventStop; ViewBag.importOptionStop = "value=" + importOption + " checked = \"checked\""; } dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Close the workbook. workbook.Close(); excelEngine.Dispose(); ViewBag.DataSource = dataTable; ViewBag.exportButtonState = ""; return(View()); } else { //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; if (saveOption == "Xls") { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2016; } //Open an existing spreadsheet which will be used as a template for generating the new spreadsheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. IWorkbook workbook; workbook = excelEngine.Excel.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; //Import data table to worksheet sheet.ImportDataTable(dataTable, true, 1, 1, true); sheet.UsedRange.AutofitColumns(); try { //Saving the workbook to disk. This spreadsheet is the result of opening and modifying //an existing spreadsheet and then saving the result to a new workbook. string ContentType = null; string fileName = null; if (saveOption == "Xlsx") { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "ExportDataTable.xlsx"; } else { ContentType = "Application/vnd.ms-excel"; fileName = "ExportDataTable.xls"; } MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, ContentType, fileName)); } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); } return(View()); }
protected void iBtnExcelImport_Click(object sender, ImageClickEventArgs e) { if (fldExcelFile.PostedFile.FileName.Equals("")) { // 업로드하실 엑셀파일을 선택해주세요. ltrScript.Text = JSHelper.GetAlertScript("업로드하실 엑셀파일을 선택해주세요.", false); return; } System.IO.Stream fileStream = null; try { fileStream = fldExcelFile.PostedFile.InputStream; } catch (Exception ex) { // 업로드 중 오류가 발생하였습니다. ltrScript.Text = JSHelper.GetAlertScript("업로드 중 오류가 발생하였습니다.", false); return; } ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; //application.UseNativeStorage = false; //user sync version 8... IWorkbook workbook = null; workbook = excelEngine.Excel.Workbooks.Open(fldExcelFile.PostedFile.InputStream); /* * try * { * workbook = excelEngine.Excel.Workbooks.Open(fldExcelFile.PostedFile.InputStream); * } * catch (Exception ex) * { * // 엑셀형식의 파일이 아닙니다. * Response.Write(ex.Message); * ltrScript.Text = JSHelper.GetAlertScript("엑셀형식의 파일이 아닙니다.", false); * excelEngine.ThrowNotSavedOnDestroy = false; * excelEngine.Dispose(); * return; * } */ IWorksheet sheet = workbook.Worksheets[0]; if (sheet.Rows.Length < 3) { // 빈 엑셀 파일입니다. ltrScript.Text = JSHelper.GetAlertScript("빈 엑셀 파일입니다.", false); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); return; } try { for (int i = 0; i < sheet.Columns.Length; i++) { string columnKey = string.Format("{0}2", DataTypeUtility.GetInt32ToAlphabet(i + 1)); sheet.Range[columnKey].Text = sheet.Range[columnKey].AddComment().Text; } DataTable ExcelUnitUploadData = new DataTable(); ExcelUnitUploadData.Columns.Add("EMP_REF_ID", typeof(int)); ExcelUnitUploadData.Columns.Add("EST_DEPT_REF_ID", typeof(string)); ExcelUnitUploadData.Columns.Add("DEPT_NAME", typeof(string)); ExcelUnitUploadData.Columns.Add("POS_CLS_ID", typeof(string)); ExcelUnitUploadData.Columns.Add("POS_CLS_NAME", typeof(string)); ExcelUnitUploadData.Columns.Add("POS_GRP_ID", typeof(string)); ExcelUnitUploadData.Columns.Add("POS_GRP_NAME", typeof(string)); ExcelUnitUploadData.Columns.Add("EMP_CODE", typeof(string)); ExcelUnitUploadData.Columns.Add("EMP_NAME", typeof(string)); ExcelUnitUploadData.Columns.Add("ORGANIZATION_POINT", typeof(double)); ExcelUnitUploadData.Columns.Add("ORGANIZATION_WEIGHT", typeof(int)); ExcelUnitUploadData.Columns.Add("APPRAISAL_POINT", typeof(double)); ExcelUnitUploadData.Columns.Add("APPRAISAL_WEIGHT", typeof(int)); ExcelUnitUploadData.Columns.Add("OTHERS1_POINT", typeof(double)); ExcelUnitUploadData.Columns.Add("OTHERS1_WEIGHT", typeof(int)); ExcelUnitUploadData.Columns.Add("OTHERS2_POINT", typeof(double)); ExcelUnitUploadData.Columns.Add("OTHERS2_WEIGHT", typeof(int)); ExcelUnitUploadData.Columns.Add("OTHERS3_POINT", typeof(double)); ExcelUnitUploadData.Columns.Add("OTHERS3_WEIGHT", typeof(int)); ExcelUnitUploadData.Columns.Add("WEIGHT_SUM", typeof(int)); ExcelUnitUploadData.Columns.Add("POINT_SUM", typeof(double)); DataTable _tmpTable = sheet.ExportDataTable(2, 1, sheet.Rows.Length, 19, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues); _iestterm_ref_id = PageUtility.GetIntByValueDropDownList(ddlEstTermInfo); _iymd = PageUtility.GetByValueDropDownList(ddlEstTermMonth, ""); DataTable _estData1 = new DataTable(); DataTable _estData2 = new DataTable(); Biz_Datas est_data = new Biz_Datas(); _estData1 = est_data.GetPersonEavluation(COMP_ID, _iestterm_ref_id, _iymd).Tables[0]; _estData2 = est_data.GetPersonEavluationPoint(COMP_ID, _iestterm_ref_id, _iymd).Tables[0]; if (_tmpTable.Rows.Count > 1 && _estData2.Rows.Count > 1) { DataRow[] _setRow; DataRow[] _setRow2; foreach (DataRow _pointRow in _tmpTable.Rows) { if (_pointRow["EST_DEPT_REF_ID"] != null && !_pointRow["EST_DEPT_REF_ID"].ToString().Equals("")) { _setRow = _estData2.Select("EST_DEPT_REF_ID = " + _pointRow["EST_DEPT_REF_ID"]); if (_setRow.Length == 1) { _pointRow["ORGANIZATION_POINT"] = _setRow[0]["ORGANIZATION_POINT"]; } else { _pointRow["ORGANIZATION_POINT"] = 0; } _setRow2 = _estData1.Select("EMP_REF_ID = " + _pointRow["EMP_REF_ID"]); if (_setRow2.Length == 1) { _pointRow["POS_GRP_ID"] = _setRow2[0]["POS_GRP_ID"].ToString(); } } } _tmpTable.AcceptChanges(); } foreach (DataRow _dr in _tmpTable.Rows) { if (_dr["EST_DEPT_REF_ID"] != null && !_dr["EST_DEPT_REF_ID"].ToString().Equals("")) { DataRow _drMain = ExcelUnitUploadData.NewRow(); _drMain["EMP_REF_ID"] = _dr["EMP_REF_ID"].ToString(); _drMain["EST_DEPT_REF_ID"] = _dr["EST_DEPT_REF_ID"].ToString(); _drMain["DEPT_NAME"] = _dr["DEPT_NAME"].ToString(); _drMain["POS_CLS_ID"] = _dr["POS_CLS_ID"].ToString(); _drMain["POS_CLS_NAME"] = _dr["POS_CLS_NAME"].ToString(); _drMain["POS_GRP_ID"] = _dr["POS_GRP_ID"].ToString(); _drMain["POS_GRP_NAME"] = _dr["POS_GRP_NAME"].ToString(); _drMain["EMP_CODE"] = _dr["EMP_CODE"].ToString(); _drMain["EMP_NAME"] = _dr["EMP_NAME"].ToString(); _drMain["ORGANIZATION_POINT"] = _dr["ORGANIZATION_POINT"].ToString(); _drMain["ORGANIZATION_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["ORGANIZATION_WEIGHT"])); _drMain["APPRAISAL_POINT"] = DataTypeUtility.GetToDouble(_dr["APPRAISAL_POINT"]); _drMain["APPRAISAL_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["APPRAISAL_WEIGHT"])); _drMain["OTHERS1_POINT"] = DataTypeUtility.GetToDouble(_dr["OTHERS1_POINT"]); _drMain["OTHERS1_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["OTHERS1_WEIGHT"])); _drMain["OTHERS2_POINT"] = DataTypeUtility.GetToDouble(_dr["OTHERS2_POINT"]); _drMain["OTHERS2_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["OTHERS2_WEIGHT"])); _drMain["OTHERS3_POINT"] = DataTypeUtility.GetToDouble(_dr["OTHERS3_POINT"]); _drMain["OTHERS3_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["OTHERS3_WEIGHT"])); _drMain["WEIGHT_SUM"] = Convert.ToInt32(Convert.ToDouble(_dr["ORGANIZATION_WEIGHT"])) + Convert.ToInt32(Convert.ToDouble(_dr["APPRAISAL_WEIGHT"])) + Convert.ToInt32(Convert.ToDouble(_dr["OTHERS1_WEIGHT"])) + Convert.ToInt32(Convert.ToDouble(_dr["OTHERS2_WEIGHT"])) + Convert.ToInt32(Convert.ToDouble(_dr["OTHERS3_WEIGHT"])); _drMain["POINT_SUM"] = DataTypeUtility.GetToDouble(_dr["ORGANIZATION_POINT"]) + DataTypeUtility.GetToDouble(_dr["APPRAISAL_POINT"]) + DataTypeUtility.GetToDouble(_dr["OTHERS1_POINT"]) + DataTypeUtility.GetToDouble(_dr["OTHERS2_POINT"]) + DataTypeUtility.GetToDouble(_dr["OTHERS3_POINT"]); ExcelUnitUploadData.Rows.Add(_drMain); } } ugrdResultTotal.Clear(); ugrdResultTotal.DataSource = ExcelUnitUploadData; ugrdResultTotal.DataBind(); foreach (UltraGridRow _ugr in ugrdResultTotal.Rows) { if (Convert.ToDouble(_ugr.Cells.FromKey("WEIGHT_SUM").Value) != 100) { _ugr.Cells.FromKey("WEIGHT_SUM").Style.ForeColor = Color.Red; } } excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); } catch (Exception ex) { Response.Write(ex); ltrScript.Text = JSHelper.GetAlertScript("업로드 중 오류가 발생하였습니다.", false); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); } }