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();
}
}
