//2、循环三个字段,将这三个字段赋值
ExcelHelper excel_helpers = new ExcelHelper(@"7.xlsx");
DataTable thTable = excel_helpers.ExcelToDataTable("sheet1", true);
3、网格
//ExcelHelper wg = new ExcelHelper(@"德州市姓名和区县.xlsx");
//DataTable wgtable = wg.ExcelToDataTable("明细", true);
thTable.Columns.Add("目录价");
thTable.Columns.Add("折扣");
thTable.Columns.Add("收入");
for (int i = 0; i < thTable.Rows.Count; i++)
{
if (thTable.Rows[i]["产品名称"].ToString() == "云空间")
{
string[] ZFCS = thTable.Rows[i]["资费参数"].ToString().Split(':');
for (int j = 0; j < ZFCS.Length; j++)
{
if (ZFCS[j].ToString().Contains("."))
{
ZFCS[j] = System.Text.RegularExpressions.Regex.Replace(ZFCS[j], @"[^\d.\d]", "");
}
else
{
ZFCS[j] = System.Text.RegularExpressions.Regex.Replace(ZFCS[j], @"[^\d%\d]", "");
}
}
thTable.Rows[i]["目录价"] = ZFCS[1];
thTable.Rows[i]["折扣"] = ZFCS[3];
string zk = System.Text.RegularExpressions.Regex.Replace(ZFCS[3], "%", "");
double num = double.Parse(ZFCS[1]) * double.Parse(zk)*0.01;
thTable.Rows[i]["收入"] = num;
}
}
for (int i = 0; i < thTable.Rows.Count; i++)
{
if (thTable.Rows[i]["资费参数"].ToString() == "-:- -数量;- -折扣:-"&& thTable.Rows[i]["产品包名称"].ToString() == "移动云")
{
thTable.Rows.RemoveAt(i);
}
}
string a = "";
for (int i = 0; i < thTable.Rows.Count; i++)
{
if (thTable.Rows[i]["客户名称"].ToString() == "德州联合医院")
{
string[] ZFCS2 = thTable.Rows[i]["资费参数"].ToString().Split(':');
string[] yuan = ZFCS2[1].Split('元');
if (ZFCS2[2].Contains("10 10G"))
{
var t = System.Text.RegularExpressions.Regex.Split(ZFCS2[2], @"(?<=\G.{1})(?!$)");
a = t[0] + t[1] + t[3];
for (int j = 0; j < ZFCS2.Length; j++)
{
if (ZFCS2[j].ToString().Contains("."))
{
ZFCS2[j] = System.Text.RegularExpressions.Regex.Replace(ZFCS2[j], @"[^\d.\d]", "");
}
else
{
ZFCS2[j] = System.Text.RegularExpressions.Regex.Replace(ZFCS2[j], @"[^\d%\d]", "");
}
}
string zk = System.Text.RegularExpressions.Regex.Replace(ZFCS2[3], "%", "");
double num = double.Parse(yuan[0]) * double.Parse(a) * double.Parse(zk) * 0.01;
thTable.Rows[i]["收入"] = num;
}
else
{
for (int j = 0; j < ZFCS2.Length; j++)
{
if (ZFCS2[j].ToString().Contains("."))
{
ZFCS2[j] = System.Text.RegularExpressions.Regex.Replace(ZFCS2[j], @"[^\d.\d]", "");
}
else
{
ZFCS2[j] = System.Text.RegularExpressions.Regex.Replace(ZFCS2[j], @"[^\d%\d]", "");
}
}
string zk = System.Text.RegularExpressions.Regex.Replace(ZFCS2[3], "%", "");
if (ZFCS2[2]== "")
{
double num = double.Parse(yuan[0]) * double.Parse(zk) * 0.01;
thTable.Rows[i]["收入"] = num;
}
else
{
double num = double.Parse(yuan[0]) * double.Parse(ZFCS2[2]) * double.Parse(zk) * 0.01;
thTable.Rows[i]["收入"] = num;
}
}
}
}
Console.ReadKey();
-----------
//读取Excel
ExcelHelper excel_helpers = new ExcelHelper(@"1.xlsx");
DataTable thTable = excel_helpers.ExcelToDataTable("sheet1", true);
List<string> lis = new List<string>();
lis.Add("60031253");
lis.Add("60108744");
lis.Add("60080069");
lis.Add("60849558");
lis.Add("60090793");
lis.Add("60080048");
lis.Add("1b414003");
lis.Add("20180717");
var thTable2 = new DataTable();
var dtResult = new DataTable();
//查询指定条件行
DataRow[] drs2 = thTable.Select("基站归属公司 = '城三'");
for (int i = 0; i < 9; i++)
{
thTable2.Columns.Add();
}
for (int i = 0; i < drs2.Length * 8; i++)
{
thTable2.Rows.Add();
}
foreach (var item in lis)
{
for (int i = 0; i < drs2.Length ; i++)
{
thTable2.Rows[i][8] = drs2[i][1];
thTable2.Rows[i][7] = "I";
thTable2.Rows[i][6] = "20870101000000";
thTable2.Rows[i][5] = DateTime.Now.AddMinutes(+30).ToString().Replace(":", "").Replace("/", "").Replace(" ", "");
thTable2.Rows[i][4] = "CQ.CQ.C3";
thTable2.Rows[i][3] = item;
thTable2.Rows[i][2] = int.Parse(drs2[i][6].ToString()).ToString("x8");
thTable2.Rows[i][1] = int.Parse(drs2[i][5].ToString()).ToString("x8");//十进制转16进制
thTable2.Rows[i][0] = "230";
}
dtResult.Merge(thTable2);
}
for (int i = dtResult.Rows.Count - 1; i >=0 ; i--)
{
if (dtResult.Rows[i][0].ToString()!="230")
{
//移除行操作
dtResul
dtResult.Rows.Remove(dtResult.Rows[i]); } }