private void parseExcelFile(object excelFile)
{
var xApp = new Microsoft.Office.Interop.Excel.Application();
xApp.Visible = false;
var xBook = xApp.Workbooks.Open(getExcel());
int sheetCount = xBook.Worksheets.Count;
DataTable dt = new System.Data.DataTable();
dt.Columns.Add("sheetnumber");
dt.Columns.Add("sheetText");
DataRow drSelect = dt.NewRow();
drSelect["sheetnumber"] = 0;
drSelect["sheetText"] = "انتخاب کنید";
dt.Rows.Add(drSelect);
dicSheet_Column = new Dictionary <int, List <indexColumn> >();
for (int i = 1; i <= sheetCount; i++)
{
var xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Worksheets.Item[i];
if (xSheet.Visible == XlSheetVisibility.xlSheetVisible)
{
DataRow dr = dt.NewRow();
dr["sheetnumber"] = i;
dr["sheetText"] = xSheet.Name;
dt.Rows.Add(dr);
dicSheet_Column.Add(i, getNewIndexColumnList());
}
}
ViewState[vs_SheetColumn] = dicSheet_Column;
drpSheets.DataSource = dt;
drpSheets.DataBind();
xBook.Close(false);
xApp.Quit();
}
private void initDGV2()
{
System.Data.DataTable dt2 = new System.Data.DataTable();
DataColumn col0 = new DataColumn("check", typeof(bool));
DataColumn col1 = new DataColumn("columns", typeof(string));
DataColumn col2 = new DataColumn("04/02", typeof(int));
DataColumn col3 = new DataColumn("04/03", typeof(string));
DataGridViewButtonColumn col4 = new DataGridViewButtonColumn();
col4.Name = "削除";
dt2.Columns.Add(col0);
dt2.Columns.Add(col1);
dt2.Columns.Add(col2);
dt2.Columns.Add(col3);
DataRow dr1 = dt2.NewRow();
DataRow dr2 = dt2.NewRow();
DataRow dr3 = dt2.NewRow();
DataRow dr4 = dt2.NewRow();
dr1[1] = "Total";
dr1[2] = 1;
dr2[1] = "割れ";
dr2[2] = 5;
dr3[1] = "へこみ";
dr3[2] = 10;
dr4[1] = "傷";
dr4[2] = 15;
dt2.Rows.Add(dr1);
dt2.Rows.Add(dr2);
dt2.Rows.Add(dr3);
dt2.Rows.Add(dr4);
dataGridView1.DataSource = dt2;
DataGridViewComboBoxColumn comboboxCol = new DataGridViewComboBoxColumn();
comboboxCol.Items.Add("コンボ1");
comboboxCol.Items.Add("コンボ2");
comboboxCol.Items.Add("コンボ3");
//DataGridView1に現在存在しているWeek列と
// 今作成したDataGridViewComboBoxColumnを入れ替える
//表示する列の名前を設定する
comboboxCol.DataPropertyName = dataGridView1.Columns["04/03"].DataPropertyName;
//以下のようにしても同じ
//column.DataPropertyName = "Week";
//現在Week列が存在している位置に挿入する
dataGridView1.Columns.Insert(dataGridView1.Columns["04/03"].Index, comboboxCol);
//今までのWeek列を削除する
dataGridView1.Columns.Remove("04/03");
//挿入した列の名前を「Week」とする
comboboxCol.Name = "Week";
}
DataTable InitDstDataTable()
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(new DataColumn("名稱", typeof(string)));
table.Columns.Add(new DataColumn("來源", typeof(string)));
table.Columns.Add(new DataColumn("索引", typeof(int)));
table.Columns["索引"].DefaultValue = 0;
string[] cols = new string[] { "系列編號", "系列代號", "品號", "品名", "產線", "工時", "系統時薪" };
string[] keys = new string[] { "編號", "代號", "品號", "品名", "產線", "工時", "單位標準工資" };
_dstRowList.Clear();
_dstRowKeywordList.Clear();
for (int i = 0; i < cols.Length; i++)
{
string col = cols[i];
DataRow row = table.NewRow();
row["名稱"] = col;
table.Rows.Add(row);
_dstRowList.Add(col, row);
_dstRowKeywordList.Add(col, keys[i]);
}
return(table);
}
///Xuất Excel
///dutp
///24/7
public DataTable MapModelToTable(List <ResultSearchPaymentDetail> data)
{
DataTable gLinks = new System.Data.DataTable();
DataRow dr = null;
gLinks.Columns.Add(new DataColumn("SỐ FT-TT", typeof(string)));
gLinks.Columns.Add(new DataColumn("SỐ HĐBH", typeof(string)));
gLinks.Columns.Add(new DataColumn("KỲ SỐ (VNĐ)", typeof(int)));
gLinks.Columns.Add(new DataColumn("TỔNG TIỀN", typeof(decimal)));
gLinks.Columns.Add(new DataColumn("THU PHÍ NGOÀI", typeof(string)));
gLinks.Columns.Add(new DataColumn("NGÀY ĐÓNG PHÍ", typeof(string)));
gLinks.Columns.Add(new DataColumn("TRẠNG THÁI", typeof(string)));
gLinks.Columns.Add(new DataColumn("NGƯỜI CẬP NHẬT", typeof(string)));
gLinks.Columns.Add(new DataColumn("NGÀY CẬP NHẬT", typeof(string)));
foreach (var row in data)
{
dr = gLinks.NewRow();
dr["SỐ FT-TT"] = row.ftttNo;
dr["SỐ HĐBH"] = row.insuranceContractNo;
dr["KỲ SỐ (VNĐ)"] = row.repaymentId;
dr["TỔNG TIỀN"] = row.totalAmount;
dr["THU PHÍ NGOÀI"] = row.isExternalRepayment == 1 ? "X" : "";
dr["NGÀY ĐÓNG PHÍ"] = row._paymentDate;
dr["TRẠNG THÁI"] = row.processStatusName;
dr["NGƯỜI CẬP NHẬT"] = row.lastUserUpdated;
dr["NGÀY CẬP NHẬT"] = row._lastDatetimeUpdated;
gLinks.Rows.Add(dr);
}
return(gLinks);
}
/// <summary>
/// 將資料列陣列貼到指定的Worksheet
/// </summary>
public static int PasteDataRows(Worksheet sheet, System.Data.DataRow[] rows, PasteDataRowsOptions options)
{
int writeRow = options.Row;
int col = options.Column;
//建立總和查詢Dictionary
Dictionary <string, decimal> sumDic = null;
if (options.IncludeSummary)
{
sumDic = new Dictionary <string, decimal>();
foreach (string sumCol in options.SummaryColumns)
{
sumDic.Add(sumCol, 0);
}
}
//逐一將資料列貼上
foreach (DataRow dr in rows)
{
//object[] objects = dr.ItemArray;
//for (int i = 0; i < objects.Length; i++)
//{
// sheet.Cells[writeRow, col + i] = objects[i];
//}
writeRow = PasteDataRow(sheet, dr, writeRow, col);
if (options.IncludeSummary)
{
foreach (string sumCol in options.SummaryColumns)
{
sumDic[sumCol] += (Convert.IsDBNull(dr[sumCol]) ? 0 : (decimal)dr[sumCol]);
}
}
//writeRow++;
}
//產生總和列
if (options.IncludeSummary && rows.Length > 0)
{
System.Data.DataTable table = rows[0].Table;
DataRow sumRow = table.NewRow();
sumRow[0] = options.SummaryPrefix;
foreach (string sumCol in options.SummaryColumns)
{
sumRow[sumCol] = sumDic[sumCol];
}
table.Rows.Add(sumRow);
writeRow = PasteDataRow(sheet, sumRow, writeRow, col);
writeRow++;
sumRow.Delete();
}
return(writeRow);
}
public static DataSet ImportFile(string filename, bool headers = true)
{
var path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
path = path.Substring(6, path.Length - 6) + @"\" + filename;
var excelApplication = new Excel.Application();
var workbook = excelApplication.Workbooks.Open(@path);
var sheets = workbook.Sheets;
DataSet dataSet = null;
if (sheets != null && sheets.Count > 0)
{
dataSet = new DataSet();
foreach (var item in sheets)
{
var sheet = (Excel.Worksheet)item;
System.Data.DataTable table = null;
if (sheet != null)
{
table = new System.Data.DataTable();
var columnCount = ((Excel.Range)sheet.UsedRange.Rows[1, Type.Missing]).Columns.Count;
var rowCount = ((Excel.Range)sheet.UsedRange.Columns[1, Type.Missing]).Rows.Count - (headers ? 1: 0);
for (int c = 0; c < columnCount; c++)
{
var cell = (Excel.Range)sheet.Cells[1, c + 1];
var column = new DataColumn(headers ? cell.Value : string.Empty);
table.Columns.Add(column);
}
for (int r = 0; r < rowCount; r++)
{
var row = table.NewRow();
for (int k = 0; k < columnCount; k++)
{
var cell = (Excel.Range)sheet.Cells[r + 1 + (headers ? 1 : 0), k + 1];
row[k] = cell.Value;
}
table.Rows.Add(row);
}
}
dataSet.Tables.Add(table);
}
}
excelApplication.Quit();
return(dataSet);
} // ImportFile()
private void initDGV1()
{
System.Data.DataTable dt = new System.Data.DataTable();
DataColumn col1 = new DataColumn("columns", typeof(string));
DataColumn col2 = new DataColumn("04/02", typeof(int));
DataColumn col3 = new DataColumn("04/03", typeof(int));
dt.Columns.Add(col1);
dt.Columns.Add(col2);
dt.Columns.Add(col3);
DataRow dr1 = dt.NewRow();
DataRow dr2 = dt.NewRow();
DataRow dr3 = dt.NewRow();
DataRow dr4 = dt.NewRow();
dr1[0] = "Total";
dr1[1] = 1;
dr1[2] = 10;
dr2[0] = "割れ";
dr2[1] = 5;
dr2[2] = 5;
dr3[0] = "へこみ";
dr3[1] = 10;
dr3[2] = 1;
dr4[0] = "傷";
dr4[1] = 15;
dr4[2] = 8;
dt.Rows.Add(dr1);
dt.Rows.Add(dr2);
dt.Rows.Add(dr3);
dt.Rows.Add(dr4);
dataGridView1.DataSource = dt;
}
private static DataTable ReadFile(string path)
{
System.Data.DataTable table = new System.Data.DataTable("dataFromFile");
table.Columns.AddRange(new DataColumn[]
{
new DataColumn("col1", typeof(string))
});
using (StreamReader sr = new StreamReader(path))
{
string line;
while ((line = sr.ReadLine()) != null)
{
DataRow tempRw = table.NewRow();
tempRw["col1"] = line;
table.Rows.Add(tempRw);
}
}
return(table);
}
DataTable AnalyzeSrcColumn(Worksheet sheet)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(new DataColumn("�W��", typeof(string)));
table.Columns.Add(new DataColumn("����", typeof(int)));
string[] columns = CaseLib.GetHeaders(_sheet, 1);
for (int i = 0; i < columns.Length; i++)
{
if (columns[i] == null || columns[i].Trim() == string.Empty)
continue;
DataRow row = table.NewRow();
row["�W��"] = columns[i];
row["����"] = i + 1;
table.Rows.Add(row);
}
return table;
}
DataTable AnalyzeSrcColumn(Worksheet sheet)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(new DataColumn("名稱", typeof(string)));
table.Columns.Add(new DataColumn("索引", typeof(int)));
string[] columns = CaseLib.GetHeaders(_sheet, 1);
for (int i = 0; i < columns.Length; i++)
{
if (columns[i] == null || columns[i].Trim() == string.Empty)
{
continue;
}
DataRow row = table.NewRow();
row["名稱"] = columns[i];
row["索引"] = i + 1;
table.Rows.Add(row);
}
return(table);
}
DataTable InitDstDataTable()
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(new DataColumn("�W��", typeof(string)));
table.Columns.Add(new DataColumn("�ӷ�", typeof(string)));
table.Columns.Add(new DataColumn("����", typeof(int)));
table.Columns["����"].DefaultValue = 0;
string[] cols = new string[] { "�t�C�s��","�t�C�N��", "�~��", "�~�W", "���u", "�u��", "�t���~" };
string[] keys = new string[] { "�s��", "�N��", "�~��", "�~�W", "���u", "�u��", "���зǤu��" };
_dstRowList.Clear();
_dstRowKeywordList.Clear();
for(int i=0;i<cols.Length;i++)
{
string col = cols[i];
DataRow row = table.NewRow();
row["�W��"] = col;
table.Rows.Add(row);
_dstRowList.Add(col, row);
_dstRowKeywordList.Add(col, keys[i]);
}
return table;
}
