public void DataTableToExcelTest()
{
List <Model.TestsTabelToListObject> testList = new List <Model.TestsTabelToListObject>
{
new Model.TestsTabelToListObject
{
Age = 10,
Height = 20.907,
Name = "qinxianbo",
Right = true,
Sex = Enum.EnumSex.Boy,
YouLong = new TimeSpan(1, 1, 1, 1),
BrityDay = new DateTime(2017, 2, 3)
},
new Model.TestsTabelToListObject
{
Age = 23,
Height = 234.907,
Name = "秦先波",
Right = true,
Sex = Enum.EnumSex.Boy,
YouLong = new TimeSpan(1, 1, 1, 2),
BrityDay = new DateTime(1994, 4, 5)
},
new Model.TestsTabelToListObject
{
Age = 40,
Height = 20.907,
Name = "qinxianbo",
Right = true,
Sex = Enum.EnumSex.Boy,
YouLong = new TimeSpan(1, 1, 1, 3),
BrityDay = new DateTime(2017, 2, 23)
},
new Model.TestsTabelToListObject
{
Height = 20.907,
Name = "杨宏俊",
Right = true,
Sex = Enum.EnumSex.Grily,
YouLong = new TimeSpan(1, 1, 1, 4),
BrityDay = new DateTime(1995, 6, 7)
},
new Model.TestsTabelToListObject
{
Age = 10,
Name = "k",
Height = 20.907,
Right = true,
Sex = Enum.EnumSex.Boy,
YouLong = new TimeSpan(1, 1, 1, 5)
}
};
var table = DataTypeConvertHelper.ToDataTable(testList);
var filePath = "..\\..\\TestUseFile\\DataTableToExcel.xlsx";
var excelHelper = new NpoiHelper(filePath);
var result = excelHelper.DataTableToExcel(table, "sheet", true);
(result - 1).IsEqualTo(testList.Count);
}
private void Btn_Dddc_Click(object sender, EventArgs e)
{
string exportpath = txt_exportpath.Text.Trim();
NpoiHelper.DataTableToExcel(dt, "mysheet", true, exportpath);
MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//导出重名
private void Btn_SameName_Click(object sender, EventArgs e)
{
string path = txt_filepath.Text.Trim();
string exportpath = txt_exportpath.Text.Trim();
TableHelper tableHelper = new TableHelper();
DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath);
DataTable table = dt.Clone();
List <string> record = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Regex reg = new Regex(@"\s/g");
string name = reg.Replace(dt.Rows[i]["name"].ToString(), "");
dt.Rows[i]["name"] = name;
if (record.Contains(name))
{
continue;
}
record.Add(name);
DataRow[] rows = dt.Select("name = '" + name + "'");
if (rows.Length > 1)
{
foreach (DataRow item in rows)
{
//解决该行已经属于另一个表
table.Rows.Add(item.ItemArray);
}
}
}
NpoiHelper.DataTableToExcel(table, "mysheet", true, exportpath);
MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void GreatExcel()
{
string exportpath = txt_endpath.Text.Trim();
if (filepath == "")
{
MessageBox.Show("请先选择文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//判断是否存在目录
TableHelper tableHelper = new TableHelper();
DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath);
DataTable table = dt.Clone();
ValidateHelper validateHelper = new ValidateHelper();
for (int i = 0; i < dt.Rows.Count; i++)
{
string idcard = dt.Rows[i]["idcard"].ToString();
string userid = dt.Rows[i]["userid"].ToString();
string username = dt.Rows[i]["username"].ToString();
string areaname = dt.Rows[i]["areaname"].ToString();
bool istrue = validateHelper.IsIdCard(idcard);
if (!istrue)
{
DataRow row = table.NewRow();
row["userid"] = userid;
row["username"] = username;
row["idcard"] = idcard;
row["areaname"] = areaname;
table.Rows.Add(row);
}
}
NpoiHelper.DataTableToExcel(table, "mysheet", true, exportpath);
MessageBox.Show("验证完成", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void GreatExcel()
{
string path = txt_filepath.Text.Trim();
string exportpath = txt_exportpath.Text.Trim();
string exportphoto = txt_exportphoto.Text.Trim();
if (path == "")
{
MessageBox.Show("文件路径不能为空", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//判断是否存在目录
if (!Directory.Exists(path))
{
MessageBox.Show("文件路径不存在", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (filepath == "")
{
MessageBox.Show("请先选择文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//判断是否存在目录
TableHelper tableHelper = new TableHelper();
DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath);
//dt = tableHelper.ReplaceData(dt, "sfzz");
//dt = tableHelper.ReplaceData(dt, "sfxr");
FileHelper fileHelper = new FileHelper();
List <DirectoryInfo> folders = fileHelper.GetAllFoldersInPath(path);
List <FileInfo> files = fileHelper.GetAllFilesInPaths(folders);
for (int i = 0; i < dt.Rows.Count; i++)
{
string name = dt.Rows[i]["name"].ToString();
string newname = dt.Rows[i]["newname"].ToString();
if (name.Contains(" "))
{
name = name.Replace(" ", "");
dt.Rows[i]["name"] = name;
}
string photo = fileHelper.IsExistFile(name, files, "default.jpg", newname, exportphoto);
dt.Rows[i]["photo"] = photo;
}
NpoiHelper.DataTableToExcel(dt, "mysheet", true, exportpath);
MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void Btn_Change_Click(object sender, EventArgs e)
{
//获取列名称
string columnname = txt_columnname.Text.Trim();
if (columnname == "")
{
MessageBox.Show("请填写列名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (!dt.Columns.Contains(columnname))
{
MessageBox.Show("不存在此列名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//条件判断，智能取其中一个
#region 条件一
if (Ckb_isuseone.Checked)
{
//查找相同 删除重复数据，并把重复数据导入到新建的excel中
if (ckb_issame.Checked)
{
string path = txt_filepath.Text.Trim();
string exportpath = txt_exportpath.Text.Trim();
DataTable table = dt.Clone();
List <string> names = new List <string>();
Regex reg = new Regex(@"\s");
for (int i = 0; i < dt.Rows.Count; i++)
{
string name = reg.Replace(dt.Rows[i][columnname].ToString(), "", 999);
//去除中间空格及左右空格，防止数据错误
dt.Rows[i][columnname] = name;
//判断是否已经处理过这名字
if (names.Contains(name))
{
continue;
}
names.Add(name);
List <DataRow> rows = dt.Select().Where(t => reg.Replace(t[columnname].ToString(), "", 999) == name).ToList();
if (rows.Count > 1)
{
foreach (DataRow item in rows)
{
//删除空格
item[columnname] = reg.Replace(item[columnname].ToString(), "", 999);
//解决该行已经属于另一个表
table.Rows.Add(item.ItemArray);
//删除重复数据
dt.Rows.Remove(item);
}
}
}
NpoiHelper.DataTableToExcel(table, "mysheet", true, exportpath);
MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
#endregion
#region 条件二
if (Ckb_isusetwo.Checked)
{
string where_one = txt_whereone.Text.Trim();
string where_two = txt_wheretwo.Text.Trim();
string where_three = txt_wherethree.Text.Trim();
for (int i = 0; i < dt.Rows.Count; i++)
{
string value = dt.Rows[i][columnname].ToString();
//去除中间空格及左右空格，防止数据错误
value = value.Trim().Replace(" ", "");
if (value == where_one)
{
value = txt_resultone.Text.Trim();
}
else if (value == where_two)
{
value = txt_resulttwo.Text.Trim();
}
else if (value == where_three)
{
value = txt_resultthree.Text.Trim();
}
dt.Rows[i][columnname] = value;
}
}
#endregion
#region 条件三
if (Ckb_isusethree.Checked)
{
//是否是身份证
if (ckb_isidcard.Checked)
{
ValidateHelper validateHelper = new ValidateHelper();
for (int i = 0; i < dt.Rows.Count; i++)
{
string value = dt.Rows[i][columnname].ToString();
//去除中间空格及左右空格，防止数据错误
value = value.Trim().Replace(" ", "");
if (value != "")
{
bool istrue = validateHelper.IsIdCard(value);
if (!istrue)
{
dt.Rows[i][columnname] = value + "数据错误";
}
}
}
}
}
#endregion
MessageBox.Show("更改成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}