private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
bool bl = false;
GridColumn gc = this.ctrlProject_Sum1.GridView.VisibleColumns[k - 1];
dt.Columns.Add(gc.FieldName);
h1.Add(aa.ToString(), gc.FieldName);
aa++;
}
int m = 1;
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
{
dt.Rows.Add(dr);
}
}
return(dt);
}
private void AddData(FarPoint.Win.Spread.FpSpread obj)
{
try
{
wait1 = new WaitDialogForm("", "正在加载数据, 请稍候...");
//打开Excel表格
//清空工作表
fpSpread1.Sheets.Clear();
obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet10_1.xls");
PF.SpreadRemoveEmptyCells(obj);
this.barEditItem1.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
this.AddCellChanged();
S10_11.AddBarEditItems(this.barEditItem1, this.barEditItem2, this);//添加下拉菜单内容
wait1.Close();
}
catch (System.Exception e)
{
//如果打开出错则重新生成并保存
LoadData();
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
{
Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
}
//保存EXcel文件
obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet10_1.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet10_1.xls";
workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.WrapText = true;
//保护工作表
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
}
wait1.Close();
}
private void btnOpenFile_Click(object sender, EventArgs e)
{
string sFile = "";
FarPoint.Win.Spread.FpSpread spdTemp = new FarPoint.Win.Spread.FpSpread();
try
{
openFileDialog1.DefaultExt = "xls";
openFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx";
if (openFileDialog1.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
txtFilePath.Text = openFileDialog1.FileName;
sFile = openFileDialog1.FileName;
spdTemp.OpenExcel(sFile, FarPoint.Excel.ExcelOpenFlags.NoFlagsSet);
spdUploadData.Sheets.RemoveAt(0);
spdUploadData.Sheets.Add(spdTemp.Sheets[0]);
spdUploadData.Sheets[0].ColumnCount = this.Key_Count + this.Value_Count;
spdUploadData.Sheets[0].RowCount = 16;
}
catch (Exception ex)
{
CmnFunction.ShowMsgBox("udcCUSReportUpload002.btnOpenFile_Click() " + "\r\n" + ex.ToString());
}
}
public static tempU::DataTable GetExcel(string filepach, IList <string> filedList, IList <string> capList)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
tempU::DataTable dt = new tempU::DataTable();
IList <string> fie = new List <string>();
int m = 3;
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[2, j].Text))
{
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)]);
}
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
dr[fie[j]] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
dt.Rows.Add(dr);
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
//}
foreach (GridColumn gc in ctrlBurdenLine1.GridView.VisibleColumns)
{
dt.Columns.Add(gc.Caption);
}
//dt.Columns.RemoveAt(27);
//dt.Columns.RemoveAt(26);
for (int i = 3; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data); j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
{
dt.Rows.Add(dr);
}
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
// dt.Columns.Add("col" + k.ToString());
GridColumn gc = this.ctrlPSP_PowerSubstationInfo1.GridView.VisibleColumns[k - 1];
dt.Columns.Add(gc.FieldName);
}
for (int i = 3; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
{
dt.Rows.Add(dr);
}
}
return(dt);
}
// catch { MsgBox.Show("导入格式不正确！"); }
//}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
for (int k = 0; k < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
//dt.Columns.Add("col" + k.ToString());
string aa = fpSpread1.Sheets[0].Cells[0, k].Value.ToString();
dt.Columns.Add(aa);
}
for (int i = 0; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
{
dt.Rows.Add(dr);
}
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
string[] col = new string[14]{"S3", "AreaName", "Title", "L1", "L2", "L3", "L4","L5", "L9","L10","S1","S2","S4","S5" };
int[] colnum = new int[7] {0,2,6,8,9,7,11 };//��������
int c = 0;
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// bool bl = false;
// GridColumn gc = this.ctrlSubstation_Info1.GridView.VisibleColumns[k - 1];
// dt.Columns.Add(gc.FieldName);
// h1.Add(aa.ToString(), gc.FieldName);
// aa++;
//}
for (int k = 0; k < col.Length; k++)
{
dt.Columns.Add(col[k]);
}
string[] cnNum = new string[9] { "һ", "��", "��", "��", "��", "��", "��", "��", "��" };
int cn=0;
int gong = 65;
int m = 2;
string L1 = "";
string S4 = "";
string L2 = "";
string AreaName = "";
string temStr="";
// string L
if (fpSpread1.Sheets[0].Cells[1, 0].Text != "���" && fpSpread1.Sheets[0].Cells[1, 0].Text != "")
m = 1;
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
c = 0;
if (fpSpread1.Sheets[0].Cells[i, 0].Text == cnNum[cn])
{
GetL1S4(fpSpread1.Sheets[0].Cells[i, 1].Text, out L1, out S4);
AreaName = "";
gong = 65;
cn++;
continue;
}
else if ((temStr=fpSpread1.Sheets[0].Cells[i, 0].Text.Replace(" ","")) == Convert.ToChar(gong).ToString().ToLower())
{
AreaName = fpSpread1.Sheets[0].Cells[i, 1].Text;
gong++;
continue;
}
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[col[colnum[c]]] = fpSpread1.Sheets[0].Cells[i, j].Text;
c++;
}
GetL2(dr["L4"].ToString(), out L2);
dr["AreaName"] = AreaName; dr["L1"] = L1; dr["S4"] = S4; dr["L2"] = L2; dr["S3"] = ""; dr["S5"] = i.ToString();
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// bool bl = false;
// GridColumn gc = this.ctrlProject_Sum1.GridView.VisibleColumns[k - 1];
// dt.Columns.Add(gc.FieldName);
// h1.Add(aa.ToString(), gc.FieldName);
// aa++;
//}
{
dt.Columns.Add("PQName");
h1.Add("0", "PQName");
dt.Columns.Add("PQtype");
h1.Add("1", "PQtype");
dt.Columns.Add("SubName");
h1.Add("2", "SubName");
dt.Columns.Add("LineName");
h1.Add("3", "LineName");
dt.Columns.Add("LineType");
h1.Add("4", "LineType");
dt.Columns.Add("LineLength");
h1.Add("5", "LineLength");
dt.Columns.Add("Num1");
h1.Add("6", "Num1");
dt.Columns.Add("Num2");
h1.Add("7", "Num2");
dt.Columns.Add("Num3");
h1.Add("8", "Num3");
dt.Columns.Add("Num4");
h1.Add("9", "Num4");
dt.Columns.Add("Num5");
h1.Add("10", "Num5");
dt.Columns.Add("Num6");
h1.Add("11", "Num6");
dt.Columns.Add("WG1");
h1.Add("12", "WG1");
dt.Columns.Add("Num7");
h1.Add("13", "Num7");
dt.Columns.Add("Num8");
h1.Add("14", "Num8");
dt.Columns.Add("WG2");
h1.Add("15", "WG2");
dt.Columns.Add("LineSX");
h1.Add("16", "LineSX");
dt.Columns.Add("col1");
h1.Add("17", "col1");
dt.Columns.Add("KBS");
h1.Add("18", "KBS");
dt.Columns.Add("KG");
h1.Add("19", "KG");
dt.Columns.Add("JXMS");
h1.Add("20", "JXMS");
dt.Columns.Add("LLXMC");
h1.Add("21", "LLXMC");
dt.Columns.Add("MaxFH");
h1.Add("22", "MaxFH");
dt.Columns.Add("SafeFH");
h1.Add("23", "SafeFH");
dt.Columns.Add("FZL");
h1.Add("24", "FZL");
}
int m = 1;
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[3 + i, j].Text;
dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[3 + i, j].Text;
}
if (str != "")
{
if (fpSpread1.Sheets[0].Cells[3 + i, 22].Text != "" && fpSpread1.Sheets[0].Cells[3 + i, 23].Text != "")
{
dr[24] = (Convert.ToDecimal(fpSpread1.Sheets[0].Cells[3 + i, 22].Text) / Convert.ToDecimal(fpSpread1.Sheets[0].Cells[3 + i, 23].Text) * 100).ToString("##.##");
}
dt.Rows.Add(dr);
}
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
IList <string> filedList = new List <string>();
IList <string> capList = new List <string>();
for (int i = 0; i < treeList1.Columns.Count; i++)
{
if (treeList1.Columns[i].VisibleIndex < 0)
{
if (treeList1.Columns[i].FieldName == "ParentID")
{
capList.Add("父ID");
}
else
{
capList.Add(treeList1.Columns[i].FieldName);
}
}
else
{
if (treeList1.Columns[i].FieldName != "Title")
{
capList.Add(treeList1.Columns[i].Caption);
}
else
{
capList.Add("项目");
}
}
filedList.Add(treeList1.Columns[i].FieldName);
}
if (!capList.Contains("父ID"))
{
capList.Add("父ID");
filedList.Add("ParentID");
}
int c = 0;
IList <string> fie = new List <string>();
int cn = 0;
int gong = 65;
int m = 3;
string L1 = "";
string S4 = "";
string L2 = "";
string AreaName = "";
string temStr = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[2, j].Text))
{
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)]);
}
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
for (int j = 0, fiej = 0; fiej < fie.Count && j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
// dr[fie[j]] = fpSpread1.Sheets[0].Cells[i, j].Text.Trim();
if (capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text) < 0)
{
// fiej = j - 1;
continue;
}
// if (fie.Contains(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)]))
// dr[fie[fie.IndexOf(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)])]] = fpSpread1.Sheets[0].Cells[i, j].Text.Trim();
dr[fie[fiej]] = fpSpread1.Sheets[0].Cells[i, j].Text.Trim();
fiej++;
}
// GetL2(dr["L4"].ToString(), out L2);
// dr["AreaName"] = AreaName; dr["L1"] = L1; dr["S4"] = S4; dr["L2"] = L2; dr["S3"] = ""; dr["S5"] = i.ToString();
// if (str != "")
dt.Rows.Add(dr);
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
for (int k = 0; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
bool bl = false;
GridColumn gc = this.ctrlSubstation_Info_LangFang1.GridView.VisibleColumns[k - 1];
//dt.Columns.Add(gc.FieldName);
//h1.Add(aa.ToString(), gc.FieldName);
//aa++;
foreach (GridColumn tlc in this.ctrlSubstation_Info_LangFang1.GridView.Columns)
{
if (tlc.Caption == fpSpread1.Sheets[0].Cells[2, k].Text && (tlc.FieldName == "L23" || tlc.FieldName == "L13" || tlc.FieldName == "L14" || tlc.FieldName == "L15" || tlc.FieldName == "L16" || tlc.FieldName == "L17" || tlc.FieldName == "L18" || tlc.FieldName == "L19" || tlc.FieldName == "L20" || tlc.FieldName == "L7" || tlc.FieldName == "L8" || tlc.FieldName == "L11" || tlc.FieldName == "L12"))
{
//if (fpSpread1.Sheets[0].Cells[1, k].Text=="500"||fpSpread1.Sheets[0].Cells[1, k].Text=="220"||fpSpread1.Sheets[0].Cells[1, k].Text=="110"||fpSpread1.Sheets[0].Cells[1, k].Text=="35"||fpSpread1.Sheets[0].Cells[1, k].Text=="6"||fpSpread1.Sheets[0].Cells[1, k].Text=="10")
{
dt.Columns.Add(tlc.FieldName);
//h1.Add(aa.ToString(), tlc.FieldName);
if (!h1.Contains(tlc.Caption))
h1.Add(tlc.Caption, tlc.FieldName);
//aa++;
}
}
//else if (tlc.Caption == fpSpread1.Sheets[0].Cells[2, k].Text && tlc.FieldName.IndexOf("��") >= 0)
//{
// dt.Columns.Add(tlc.FieldName);
//}
else if (tlc.Caption == fpSpread1.Sheets[0].Cells[0, k].Text)
{
dt.Columns.Add(tlc.FieldName);
h1.Add(tlc.Caption, tlc.FieldName);
//h1.Add(aa.ToString(), tlc.FieldName);
//aa++;
}
}
}
this.ctrlSubstation_Info_LangFang1.GridView.SaveLayoutToXml(@"C:\Documents and Settings\Administrator\����\aauuyy.xml");
int m = 1;
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
try
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
//dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
//dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
if (h1[fpSpread1.Sheets[0].Cells[2, j].Text] != null)
dr[h1[fpSpread1.Sheets[0].Cells[2, j].Text].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
catch { }
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
//}
foreach (GridColumn gc in ctrlBurdenMonth1.GridView.VisibleColumns)
dt.Columns.Add(gc.Caption);
for (int i = 3; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data); j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
for (int k = 0; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
bool bl = false;
GridColumn gc = this.ctrlSubstation_Info_LangFang1.GridView.VisibleColumns[k - 1];
//dt.Columns.Add(gc.FieldName);
//h1.Add(aa.ToString(), gc.FieldName);
//aa++;
foreach (GridColumn tlc in this.ctrlSubstation_Info_LangFang1.GridView.Columns)
{
if (tlc.Caption == fpSpread1.Sheets[0].Cells[2, k].Text && (tlc.FieldName == "L23" || tlc.FieldName == "L13" || tlc.FieldName == "L14" || tlc.FieldName == "L15" || tlc.FieldName == "L16" || tlc.FieldName == "L17" || tlc.FieldName == "L18" || tlc.FieldName == "L19" || tlc.FieldName == "L20" || tlc.FieldName == "L7" || tlc.FieldName == "L8" || tlc.FieldName == "L11" || tlc.FieldName == "L12"))
{
//if (fpSpread1.Sheets[0].Cells[1, k].Text=="500"||fpSpread1.Sheets[0].Cells[1, k].Text=="220"||fpSpread1.Sheets[0].Cells[1, k].Text=="110"||fpSpread1.Sheets[0].Cells[1, k].Text=="35"||fpSpread1.Sheets[0].Cells[1, k].Text=="6"||fpSpread1.Sheets[0].Cells[1, k].Text=="10")
{
dt.Columns.Add(tlc.FieldName);
//h1.Add(aa.ToString(), tlc.FieldName);
if (!h1.Contains(tlc.Caption))
{
h1.Add(tlc.Caption, tlc.FieldName);
}
//aa++;
}
}
//else if (tlc.Caption == fpSpread1.Sheets[0].Cells[2, k].Text && tlc.FieldName.IndexOf("年") >= 0)
//{
// dt.Columns.Add(tlc.FieldName);
//}
else if (tlc.Caption == fpSpread1.Sheets[0].Cells[0, k].Text)
{
dt.Columns.Add(tlc.FieldName);
h1.Add(tlc.Caption, tlc.FieldName);
//h1.Add(aa.ToString(), tlc.FieldName);
//aa++;
}
}
}
this.ctrlSubstation_Info_LangFang1.GridView.SaveLayoutToXml(@"C:\Documents and Settings\Administrator\桌面\aauuyy.xml");
int m = 1;
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
try
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
//dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
//dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
if (h1[fpSpread1.Sheets[0].Cells[2, j].Text] != null)
{
dr[h1[fpSpread1.Sheets[0].Cells[2, j].Text].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
}
catch { }
}
if (str != "")
{
dt.Rows.Add(dr);
}
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// bool bl = false;
// GridColumn gc = this.ctrlProject_Sum1.GridView.VisibleColumns[k - 1];
// dt.Columns.Add(gc.FieldName);
// h1.Add(aa.ToString(), gc.FieldName);
// aa++;
//}
{
dt.Columns.Add("PQName");
h1.Add("0", "PQName");
dt.Columns.Add("PQtype");
h1.Add("1", "PQtype");
dt.Columns.Add("SubName");
h1.Add("2", "SubName");
dt.Columns.Add("LineName");
h1.Add("3", "LineName");
dt.Columns.Add("LineType");
h1.Add("4", "LineType");
dt.Columns.Add("LineLength");
h1.Add("5", "LineLength");
dt.Columns.Add("Num1");
h1.Add("6", "Num1");
dt.Columns.Add("Num2");
h1.Add("7", "Num2");
dt.Columns.Add("Num3");
h1.Add("8", "Num3");
dt.Columns.Add("Num4");
h1.Add("9", "Num4");
dt.Columns.Add("Num5");
h1.Add("10", "Num5");
dt.Columns.Add("Num6");
h1.Add("11", "Num6");
dt.Columns.Add("WG1");
h1.Add("12", "WG1");
dt.Columns.Add("Num7");
h1.Add("13", "Num7");
dt.Columns.Add("Num8");
h1.Add("14", "Num8");
dt.Columns.Add("WG2");
h1.Add("15", "WG2");
dt.Columns.Add("LineSX");
h1.Add("16", "LineSX");
dt.Columns.Add("col1");
h1.Add("17", "col1");
dt.Columns.Add("KBS");
h1.Add("18", "KBS");
dt.Columns.Add("KG");
h1.Add("19", "KG");
dt.Columns.Add("JXMS");
h1.Add("20", "JXMS");
dt.Columns.Add("LLXMC");
h1.Add("21", "LLXMC");
dt.Columns.Add("MaxFH");
h1.Add("22", "MaxFH");
dt.Columns.Add("SafeFH");
h1.Add("23", "SafeFH");
dt.Columns.Add("FZL");
h1.Add("24", "FZL");
}
int m = 1;
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[3 + i, j].Text;
dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[3 + i, j].Text;
}
if (str != "")
{
if (fpSpread1.Sheets[0].Cells[3 + i, 22].Text != "" && fpSpread1.Sheets[0].Cells[3 + i, 23].Text != "")
{
dr[24] = (Convert.ToDecimal(fpSpread1.Sheets[0].Cells[3 + i, 22].Text) / Convert.ToDecimal(fpSpread1.Sheets[0].Cells[3 + i, 23].Text)*100).ToString("##.##");
}
dt.Rows.Add(dr);
}
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
// dt.Columns.Add("col" + k.ToString());
GridColumn gc = this.ctrlPSP_PowerSubstationInfo1.GridView.VisibleColumns[k - 1];
dt.Columns.Add(gc.FieldName);
}
for (int i = 3; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) ; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
string[] col = new string[14] {
"S3", "AreaName", "Title", "L1", "L2", "L3", "L4", "L5", "L9", "L10", "S1", "S2", "S4", "S5"
};
int[] colnum = new int[7] {
0, 2, 6, 8, 9, 7, 11
}; //导出的列
int c = 0;
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// bool bl = false;
// GridColumn gc = this.ctrlSubstation_Info1.GridView.VisibleColumns[k - 1];
// dt.Columns.Add(gc.FieldName);
// h1.Add(aa.ToString(), gc.FieldName);
// aa++;
//}
for (int k = 0; k < col.Length; k++)
{
dt.Columns.Add(col[k]);
}
string[] cnNum = new string[9] {
"一", "二", "三", "四", "五", "六", "七", "八", "九"
};
int cn = 0;
int gong = 65;
int m = 2;
string L1 = "";
string S4 = "";
string L2 = "";
string AreaName = "";
string temStr = "";
// string L
if (fpSpread1.Sheets[0].Cells[1, 0].Text != "序号" && fpSpread1.Sheets[0].Cells[1, 0].Text != "")
{
m = 1;
}
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
c = 0;
if (fpSpread1.Sheets[0].Cells[i, 0].Text == cnNum[cn])
{
GetL1S4(fpSpread1.Sheets[0].Cells[i, 1].Text, out L1, out S4);
AreaName = "";
gong = 65;
cn++;
continue;
}
else if ((temStr = fpSpread1.Sheets[0].Cells[i, 0].Text.Replace(" ", "")) == Convert.ToChar(gong).ToString().ToLower())
{
AreaName = fpSpread1.Sheets[0].Cells[i, 1].Text;
gong++;
continue;
}
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[col[colnum[c]]] = fpSpread1.Sheets[0].Cells[i, j].Text;
c++;
}
GetL2(dr["L4"].ToString(), out L2);
dr["AreaName"] = AreaName; dr["L1"] = L1; dr["S4"] = S4; dr["L2"] = L2; dr["S3"] = ""; dr["S5"] = i.ToString();
if (str != "")
{
dt.Rows.Add(dr);
}
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
////////for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
////////{
//////// bool bl = false;
//////// foreach (GridBand gb in this.ctrlLine_Info1.GridView.Bands)
//////// {
//////// if (gb.Caption == fpSpread1.Sheets[0].Cells[0, k - 1].Text && gb.Columns.Count == 1)
//////// {
//////// if (type2 == "66" && gb.Columns[0].FieldName == "K1")
//////// continue;
//////// if (type2 == "10" && gb.Columns[0].FieldName == "L1")
//////// continue;
//////// dt.Columns.Add(gb.Columns[0].FieldName);
//////// h1.Add(aa.ToString(), gb.Columns[0].FieldName);
//////// aa++;
//////// }
//////// }
////////}
for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
bool bl = false;
GridColumn gc = this.ctrlLine_Info1.GridView.VisibleColumns[k - 1];
dt.Columns.Add(gc.FieldName);
h1.Add(aa.ToString(), gc.FieldName);
aa++;
}
int m = 0;
if (type2 == "10")
{
m = 1;
}
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
{
dt.Rows.Add(dr);
}
}
return(dt);
}
private DataTable GetExcel(string filepach)
{
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
IList <string> filedList = new List <string>();
IList <string> capList = new List <string>();
for (int i = 0; i < treeList1.Columns.Count; i++)
{
if (treeList1.Columns[i].VisibleIndex < 0)
{
if (treeList1.Columns[i].FieldName == "ParentID")
{
capList.Add("父ID");
}
else
{
capList.Add(treeList1.Columns[i].FieldName);
}
}
else
{
if (treeList1.Columns[i].FieldName != "Title")
{
capList.Add(treeList1.Columns[i].Caption);
}
else
{
capList.Add("项目");
}
}
filedList.Add(treeList1.Columns[i].FieldName);
}
int c = 0;
IList <string> fie = new List <string>();
int m = 3;
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[2, j].Text))
{
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)]);
}
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
for (int j = 0, fiej = 0; fiej < fie.Count && j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text) < 0)
{
continue;
}
dr[fie[fiej]] = fpSpread1.Sheets[0].Cells[i, j].Text.Trim();
fiej++;
}
dt.Rows.Add(dr);
}
fpSpread1.Dispose();
return(dt);
}
private DataTable GetExcel(string filepach)
{
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
IList<string> filedList = new List<string>();
IList<string> capList = new List<string>();
for (int i = 0; i < treeList1.Columns.Count; i++)
{
if (treeList1.Columns[i].VisibleIndex < 0)
{
if (treeList1.Columns[i].FieldName == "ParentID")
capList.Add("父ID");
else
{
capList.Add(treeList1.Columns[i].FieldName);
}
}
else
{
if (treeList1.Columns[i].FieldName != "Title")
capList.Add(treeList1.Columns[i].Caption);
else
{
capList.Add("项目");
}
}
filedList.Add(treeList1.Columns[i].FieldName);
}
if ( !capList.Contains("父ID"))
{
capList.Add("父ID");
filedList.Add("ParentID");
}
int c = 0;
IList<string> fie = new List<string>();
int cn = 0;
int gong = 65;
int m = 3;
string L1 = "";
string S4 = "";
string L2 = "";
string AreaName = "";
string temStr = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[2, j].Text))
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)]);
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
for (int j = 0, fiej = 0; fiej < fie.Count && j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
// dr[fie[j]] = fpSpread1.Sheets[0].Cells[i, j].Text.Trim();
if (capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text) < 0)
{
// fiej = j - 1;
continue;
}
// if (fie.Contains(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)]))
// dr[fie[fie.IndexOf(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)])]] = fpSpread1.Sheets[0].Cells[i, j].Text.Trim();
dr[fie[fiej]] = fpSpread1.Sheets[0].Cells[i, j].Text.Trim();
fiej++;
}
// GetL2(dr["L4"].ToString(), out L2);
// dr["AreaName"] = AreaName; dr["L1"] = L1; dr["S4"] = S4; dr["L2"] = L2; dr["S3"] = ""; dr["S5"] = i.ToString();
// if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
////////for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
////////{
//////// bool bl = false;
//////// foreach (GridBand gb in this.ctrlLine_Info1.GridView.Bands)
//////// {
//////// if (gb.Caption == fpSpread1.Sheets[0].Cells[0, k - 1].Text && gb.Columns.Count == 1)
//////// {
//////// if (type2 == "66" && gb.Columns[0].FieldName == "K1")
//////// continue;
//////// if (type2 == "10" && gb.Columns[0].FieldName == "L1")
//////// continue;
//////// dt.Columns.Add(gb.Columns[0].FieldName);
//////// h1.Add(aa.ToString(), gb.Columns[0].FieldName);
//////// aa++;
//////// }
//////// }
////////}
for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
bool bl = false;
GridColumn gc = this.ctrlLine_Info1.GridView.VisibleColumns[k - 1];
dt.Columns.Add(gc.FieldName);
h1.Add(aa.ToString(), gc.FieldName);
aa++;
}
int m = 0;
if (type2 == "10")
m = 1;
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
//for (int k = 1; k < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// //dt.Columns.Add("col" + k.ToString());
//}
for (int k = 0; k <=fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) ; k++)
{
foreach (TreeListColumn tlc in this.treeList1.Columns)
{
if (tlc.Caption == fpSpread1.Sheets[0].Cells[3, k].Text && (tlc.FieldName == "RongLiang" || tlc.FieldName == "ChangDu"))
{
dt.Columns.Add(tlc.FieldName);
}
else if (tlc.Caption == fpSpread1.Sheets[0].Cells[3, k].Text && tlc.FieldName.IndexOf("年") >= 0)
{
dt.Columns.Add(tlc.FieldName);
}
else if (tlc.Caption == fpSpread1.Sheets[0].Cells[2, k].Text)
{
dt.Columns.Add(tlc.FieldName);
}
}
////TreeListColumn gc = this.treeList1.Columns[k];
////if (gc.VisibleIndex != -1)
//// dt.Columns.Add(gc.FieldName);
}
for (int i =4; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
string[] col = new string[14]{"S3", "AreaName", "Title", "L1", "L2", "L3", "L4","L5", "L9","L10","S1","S2","S4","S5" };
IList<string> filedList = new List<string>();
IList<string> capList = new List<string>();
for (int i = 0; i < ctrlSubstation_Info1.GridView.Bands.Count; i++)
{
if (ctrlSubstation_Info1.GridView.Bands[i].Children.Count > 0)
{
for (int j = 0; j < ctrlSubstation_Info1.GridView.Bands[i].Children.Count; j++)
{
capList.Add(ctrlSubstation_Info1.GridView.Bands[i].Children[j].Caption);
filedList.Add(ctrlSubstation_Info1.GridView.Bands[i].Children[j].Columns[0].FieldName);
}
continue;
}
capList.Add(ctrlSubstation_Info1.GridView.Bands[i].Caption);
filedList.Add(ctrlSubstation_Info1.GridView.Bands[i].Columns[0].FieldName);
}
int[] colnum = new int[7] {0,2,6,8,9,7,11 };//��������
int c = 0;
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// bool bl = false;
// GridColumn gc = this.ctrlSubstation_Info1.GridView.VisibleColumns[k - 1];
// dt.Columns.Add(gc.FieldName);
// h1.Add(aa.ToString(), gc.FieldName);
// aa++;
//}
IList<string> fie = new List<string>();
int cn=0;
int gong = 65;
int m = 1;
string L1 = "";
string S4 = "";
string L2 = "";
string AreaName = "";
string temStr="";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if(capList.Contains(fpSpread1.Sheets[0].Cells[0, j].Text))
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[0, j].Text)]);
if (fpSpread1.Sheets[0].Cells[0, j].Text == "" && m==1)
{
fie.Add("L13"); fie.Add("L14"); fie.Add("S6");
m = 2;
}
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
dr[fie[j]] = fpSpread1.Sheets[0].Cells[i, j].Text;
try
{
if (fie[j] == "S6")
{
if (dr[fie[j]].ToString() == "")
dr[fie[j]] = string.Format("{0:f1}",double.Parse(dr["L14"].ToString())*100 / double.Parse(dr["L13"].ToString()));
}
if (fie[j] == "L10")
{
if (dr[fie[j]].ToString() == "")
dr[fie[j]] = string.Format("{0:f1}",double.Parse(dr["L9"].ToString())*100 / double.Parse(dr["L2"].ToString()));
}
}
catch { }
}
// GetL2(dr["L4"].ToString(), out L2);
// dr["AreaName"] = AreaName; dr["L1"] = L1; dr["S4"] = S4; dr["L2"] = L2; dr["S3"] = ""; dr["S5"] = i.ToString();
// if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
int sheetcount = 0;
if (fpSpread1.Sheets.Count > 1)
sheetcount = 1;
for (int k = 0; k <= fpSpread1.Sheets[sheetcount].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
bool bl = false;
try
{
foreach (TreeListColumn tlc in treeList1.Columns)
{
if (tlc.Caption == fpSpread1.Sheets[sheetcount].Cells[2, k].Text)
{
if (!h1.Contains(tlc.Caption))
{
dt.Columns.Add(tlc.FieldName);
h1.Add(tlc.Caption, tlc.FieldName);
//aa++;
}
}
else
if (fpSpread1.Sheets[sheetcount].Cells[2, k].Text.IndexOf("��")>0)
{
try
{
int maonth = Convert.ToInt32(fpSpread1.Sheets[sheetcount].Cells[2, k].Text.Replace("��",""));
if (maonth>0&&maonth<13)
if (!h1.Contains(maonth))
{
dt.Columns.Add(maonth.ToString());
h1.Add(fpSpread1.Sheets[sheetcount].Cells[2, k].Text, maonth);
//aa++;
}
}
catch { }
}
}
}
catch (Exception e)
{ //MessageBox.Show(e.Message+" "+gridcolumn);
}
}
int m = 3;
for (int i = m; i < fpSpread1.Sheets[sheetcount].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) - 4; i++)
{
try
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[sheetcount].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data); j++)
// for (int j = 0; j < h1.Count; j++)
{
str = str + fpSpread1.Sheets[sheetcount].Cells[i, j].Text;
if (h1[fpSpread1.Sheets[sheetcount].Cells[2, j].Text] != null)
if(i%4==3)
dr[h1[fpSpread1.Sheets[sheetcount].Cells[2, j].Text].ToString()] = fpSpread1.Sheets[sheetcount].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
catch (Exception ex)
{
System.Console.WriteLine(ex.Message);
}
}
return dt;
////////int m = 3;
////////for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
////////{
//////// DataRow dr = dt.NewRow();
//////// str = "";
//////// for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data)+1 ; j++)
//////// {
//////// str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
//////// dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
//////// }
//////// if (str != "")
//////// dt.Rows.Add(dr);
////////}
////////return dt;
}
private DataTable GetExcel(string filepach)
{
//string str;
//FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
//try
//{
// fpSpread1.OpenExcel(filepach);
//}
//catch
//{
// string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
// File.Copy(filepach, filepath1);
// fpSpread1.OpenExcel(filepath1);
// File.Delete(filepath1);
//}
//DataTable dt = new DataTable();
//Hashtable h1 = new Hashtable();
//int aa = 0;
//string[] col = new string[7] { "S2", "Title", "L1","L4", "L5","S4","S5" };
//int[] colnum = new int[4] { 0, 1, 3, 4 };//��������
//int c = 0;
////for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
////{
//// bool bl = false;
//// GridColumn gc = this.ctrlSubstation_Info1.GridView.VisibleColumns[k - 1];
//// dt.Columns.Add(gc.FieldName);
//// h1.Add(aa.ToString(), gc.FieldName);
//// aa++;
////}
//for (int k = 0; k < col.Length; k++)
//{
// dt.Columns.Add(col[k]);
//}
//string[] cnNum = new string[9] { "һ", "��", "��", "��", "��","��","��","��","��"};
//int cn = 0;
//int gong = 65;
//int m = 2;
//string L1 = "";
//string S4 = "";
//string L2 = "";
//string AreaName = "";
//string temStr = "";
//// string L
//if (fpSpread1.Sheets[0].Cells[1, 0].Text != "���" && fpSpread1.Sheets[0].Cells[1, 0].Text != "")
// m = 1;
//for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
//{
// c = 0;
// if (fpSpread1.Sheets[0].Cells[i, 0].Text.IndexOf(cnNum[cn])!=-1)
// {
// GetL1S4(fpSpread1.Sheets[0].Cells[i, 1].Text, out L1, out S4);
// // AreaName = "";
// // gong = 65;
// cn++;
// continue;
// }
// //else if ((temStr = fpSpread1.Sheets[0].Cells[i, 0].Text.Replace(" ", "")) == Convert.ToChar(gong).ToString().ToLower())
// //{
// // AreaName = fpSpread1.Sheets[0].Cells[i, 1].Text;
// // gong++;
// // continue;
// //}
// DataRow dr = dt.NewRow();
// str = "";
// for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
// {
// str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
// dr[col[colnum[c]]] = fpSpread1.Sheets[0].Cells[i, j].Text;
// c++;
// }
// // GetL2(dr["L4"].ToString(), out L2);
// dr["L1"] = L1; dr["S4"] = S4; dr["S2"] = ""; dr["S5"] = i.ToString();
// if (str != "")
// dt.Rows.Add(dr);
//}
//return dt;
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
IList<string> filedList = new List<string>();
IList<string> capList = new List<string>();
for (int i = 0; i < ctrlLine_Info1.GridView.Bands.Count; i++)
{
if (ctrlLine_Info1.GridView.Bands[i].Children.Count > 0)
{
for (int j = 0; j < ctrlLine_Info1.GridView.Bands[i].Children.Count; j++)
{
capList.Add(ctrlLine_Info1.GridView.Bands[i].Children[j].Caption);
filedList.Add(ctrlLine_Info1.GridView.Bands[i].Children[j].Columns[0].FieldName);
}
continue;
}
capList.Add(ctrlLine_Info1.GridView.Bands[i].Caption);
filedList.Add(ctrlLine_Info1.GridView.Bands[i].Columns[0].FieldName);
}
int[] colnum = new int[7] { 0, 2, 6, 8, 9, 7, 11 };//��������
int c = 0;
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// bool bl = false;
// GridColumn gc = this.ctrlSubstation_Info1.GridView.VisibleColumns[k - 1];
// dt.Columns.Add(gc.FieldName);
// h1.Add(aa.ToString(), gc.FieldName);
// aa++;
//}
IList<string> fie = new List<string>();
int cn = 0;
int gong = 65;
int m = 1;
string L1 = "";
string S4 = "";
string L2 = "";
string AreaName = "";
string temStr = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[0, j].Text))
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[0, j].Text)]);
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
dr[fie[j]] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
for (int k = 0; k < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
//dt.Columns.Add("col" + k.ToString());
//string aa = fpSpread1.Sheets[0].Cells[0, k].Value.ToString();
//dt.Columns.Add(aa);
foreach (TreeListColumn tlc in this.treeList1.Columns)
{
if (tlc.Caption == fpSpread1.Sheets[0].Cells[2, k].Text)
{
if (!h1.Contains(tlc.Caption))
{
dt.Columns.Add(tlc.Caption);
h1.Add(tlc.Caption, tlc.FieldName);
}
//aa++;
}
}
}
for (int i =3; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
if (h1[fpSpread1.Sheets[0].Cells[2, j].Text]!=null)
dr[fpSpread1.Sheets[0].Cells[2, j].Text] = fpSpread1.Sheets[0].Cells[i, j].Text;
// dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
bool bl = false;
GridColumn gc = this.ctrlSubstation_Info1.GridView.VisibleColumns[k - 1];
dt.Columns.Add(gc.FieldName);
h1.Add(aa.ToString(), gc.FieldName);
aa++;
}
int m = 3;
for (int i = m; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[h1[j.ToString()].ToString()] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
public static tempU::DataTable GetExcel(string filepach, IList<string> filedList, IList<string> capList)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
tempU::DataTable dt = new tempU::DataTable();
IList<string> fie = new List<string>();
int m = 3;
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[2, j].Text))
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[2, j].Text)]);
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
dr[fie[j]] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
for (int k = 0; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
{
dt.Columns.Add(fpSpread1.Sheets[0].Cells[0, k].Text);
}
for (int i =1; i < fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; i++)
{
DataRow dr = dt.NewRow();
str = "";
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
str = str + fpSpread1.Sheets[0].Cells[i, j].Text;
dr[j] = fpSpread1.Sheets[0].Cells[i, j].Text;
}
if (str != "")
dt.Rows.Add(dr);
}
return dt;
}
private DataTable GetExcel(string filepach)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
Hashtable h1 = new Hashtable();
int aa = 0;
string[] col = new string[14]{"S20", "AreaName", "Title", "L1", "L2", "L3", "L4","L5", "L9","L10","S1","S2","S4","S5" };
IList<string> filedList = new List<string>();
IList<string> capList = new List<string>();
for (int i = 0; i < ctrlSubstation_Info1.GridView.Columns.Count; i++)
{
capList.Add(ctrlSubstation_Info1.GridView.Columns[i].Caption);
filedList.Add(ctrlSubstation_Info1.GridView.Columns[i].FieldName);
}
int[] colnum = new int[7] {0,2,6,8,9,7,11 };//��������
int c = 0;
//for (int k = 1; k <= fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; k++)
//{
// bool bl = false;
// GridColumn gc = this.ctrlSubstation_Info1.GridView.VisibleColumns[k - 1];
// dt.Columns.Add(gc.FieldName);
// h1.Add(aa.ToString(), gc.FieldName);
// aa++;
IList<string> fie = new List<string>();
IList<int> yxzl = new List<int>();//��Ч���Ǽ���
int m = 1;
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[0, j].Text))
{
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[0, j].Text)]);
yxzl.Add(j);
}
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
// for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
int n = 0;
foreach (int j in yxzl)
{
dr[fie[n]] = fpSpread1.Sheets[0].Cells[i, j].Text;
n++;
}
dt.Rows.Add(dr);
}
// GetL2(dr["L4"].ToString(), out L2);
// dr["AreaName"] = AreaName; dr["L1"] = L1; dr["S4"] = S4; dr["L2"] = L2; dr["S3"] = ""; dr["S5"] = i.ToString();
// if (str != "")
return dt;
}