using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace WBuilder
{
public partial class Form1 : Form
{
DataTable dt = null;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
if (this.openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
this.textBoxPath.Text = this.openFileDialog1.FileName;
}
}
private void buttonFloder_Click(object sender, EventArgs e)
{
if (this.folderBrowserDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
this.textBoxFloder.Text = this.folderBrowserDialog1.SelectedPath;
}
}
private void button2_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(this.textBoxFloder.Text))
{
MessageBox.Show("替换目录地址不能为空!");
return;
}
if (string.IsNullOrWhiteSpace(this.textBoxPath.Text))
{
MessageBox.Show("Excel地址不能为空!");
return;
}
ExcelEx ex = new ExcelEx();
dt = ex.ImportFromExcels(this.textBoxPath.Text);
if (dt != null && dt.Rows.Count > 0)
{
if (Directory.Exists(this.textBoxFloder.Text))
{
string srcPath = this.textBoxFloder.Text;
string aimPath = string.Empty;
aimPath = srcPath + "temp";
if (Directory.Exists(aimPath))
{
Directory.Delete(aimPath, true);
}
Directory.CreateDirectory(aimPath);
CopyDir(srcPath, aimPath);
MessageBox.Show("成功!");
}
else
{
MessageBox.Show("替换目录地址不存在!");
return;
}
}
else
{
MessageBox.Show("替换内容不能为空!");
return;
}
}
private void CopyDir(string srcPath, string aimPath)
{
try
{
// 检查目标目录是否以目录分割字符结束如果不是则添加
if (aimPath[aimPath.Length - 1] != System.IO.Path.DirectorySeparatorChar)
{
aimPath += System.IO.Path.DirectorySeparatorChar;
}
// 判断目标目录是否存在如果不存在则新建
if (!System.IO.Directory.Exists(aimPath))
{
System.IO.Directory.CreateDirectory(aimPath);
}
// 得到源目录的文件列表,该里面是包含文件以及目录路径的一个数组
// 如果你指向copy目标文件下面的文件而不包含目录请使用下面的方法
// string[] fileList = Directory.GetFiles(srcPath);
string[] fileList = System.IO.Directory.GetFileSystemEntries(srcPath);
// 遍历所有的文件和目录
foreach (string file in fileList)
{
// 先当作目录处理如果存在这个目录就递归Copy该目录下面的文件
if (System.IO.Directory.Exists(file))
{
CopyDir(file, aimPath + System.IO.Path.GetFileName(file));
}
// 否则直接Copy文件
else
{
System.Text.Encoding Encoding = GetFileEncodeType(file);
string Template = File.ReadAllText(file, Encoding);
string oldValue = string.Empty;
string newValue = string.Empty;
foreach (DataRow dr in dt.Rows)
{
if (dr["oldValue"] != null && dr["oldValue"] != DBNull.Value && dr["newValue"] != null && dr["newValue"] != DBNull.Value)
{
if (dr["oldValue"] != null && dr["oldValue"] != DBNull.Value)
{
oldValue = dr["oldValue"].ToString();
}
if (dr["newValue"] != null && dr["newValue"] != DBNull.Value)
{
newValue = dr["newValue"].ToString();
}
Template = Template.Replace(oldValue.Replace("\n","\r\n"), newValue);
}
}
File.WriteAllText(aimPath + System.IO.Path.GetFileName(file), Template, Encoding);
//System.IO.File.Copy(file, aimPath + System.IO.Path.GetFileName(file), true);
}
}
}
catch (Exception e)
{
throw;
}
}
public System.Text.Encoding GetFileEncodeType(string filename)
{
System.IO.FileStream fs = new System.IO.FileStream(filename, System.IO.FileMode.Open, System.IO.FileAccess.Read);
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
Byte[] buffer = br.ReadBytes(2);
if (buffer[0] >= 0xEF)
{
if (buffer[0] == 0xEF && buffer[1] == 0xBB)
{
return System.Text.Encoding.UTF8;
}
else if (buffer[0] == 0xFE && buffer[1] == 0xFF)
{
return System.Text.Encoding.BigEndianUnicode;
}
else if (buffer[0] == 0xFF && buffer[1] == 0xFE)
{
return System.Text.Encoding.Unicode;
}
else
{
return System.Text.Encoding.Default;
}
}
else
{
return System.Text.Encoding.Default;
}
}
}
}
namespace WBuilder
{
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;
using System.Reflection;
public class ExcelEx
{
private System.Windows.Forms.OpenFileDialog openFileDlg;
private System.Windows.Forms.OpenFileDialog openFileDlgcsv;
private System.Windows.Forms.SaveFileDialog saveFileDlg;
// private string savefileter;
public string SaveFileter
{
get { return this.saveFileDlg.Filter; }
set { this.saveFileDlg.Filter = value; }
}
public string SaveName
{
get { return this.saveFileDlg.FileName; }
set { this.saveFileDlg.FileName = value; }
}
private bool shortdatetime = false;
public bool Shortdatetime
{
get { return shortdatetime; }
set { shortdatetime = value; }
}
//初始化
public ExcelEx()
{
openFileDlg = new System.Windows.Forms.OpenFileDialog();
openFileDlgcsv = new System.Windows.Forms.OpenFileDialog();
saveFileDlg = new System.Windows.Forms.SaveFileDialog();
this.openFileDlg.DefaultExt = "xls";
this.openFileDlg.Filter = "Excel文件 (*.xls,*.xlsx)|*.xls;*.xlsx";
//this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls|Excel文件 (*.xlsx)|*.xlsx|CSV文件 (*.csv)|*.csv";
this.openFileDlgcsv.DefaultExt = "csv";
this.openFileDlgcsv.Filter = "Excel文件 (*.csv)|*.csv";
this.saveFileDlg.DefaultExt = "xls";
this.saveFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
}
#region 文件导入
/// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
///
public DataTable ImportFromExcel()
{
DataTable dt = new DataTable();
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
dt = doImport(openFileDlg.FileName, listtable(openFileDlg.FileName)[0].ToString());
}
return dt;
}
public DataTable ImportFromExcels(string filename)
{
DataTable dt = new DataTable();
dt = doImport(filename, listtable(filename)[0].ToString());
return dt;
}
/// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
///
public DataTable ImportFromExcel(string tablename)
{
DataTable dt = new DataTable();
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
dt = doImport(openFileDlg.FileName, tablename);
return dt;
}
/// <summary>
/// 从指定的Excel文件导入
/// </summary>
/// <param name="strFileName">Excel文件名</param>
/// <returns></returns>
///
public DataTable ImportFromExcel(string strFileName, string tablename)
{
return doImport(strFileName, tablename);
}
/// <summary>
/// 执行导入
/// </summary>
/// <param name="strFileName">文件名</param>
/// <returns>DataSet</returns>
private DataTable doImport(string strFileName, string tablename)
{
if (strFileName == "") return null;
if (tablename.Substring(tablename.Length - 1, 1) != "$") tablename += "$";
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [" + tablename + "]", getcon(strFileName));
DataTable dt = new DataTable();
try
{
ExcelDA.Fill(dt);
}
catch (Exception err)
{
throw err;
}
return dt;
}
/// <summary>
/// 列出excel的表
/// </summary>
/// <param name="strFileName">文件名</param>
public string[] listtable(string strFileName)
{
if (strFileName == "") return null;
OleDbConnection objConn;
DataTable dt;
objConn = getcon(strFileName);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
objConn.Close();
string[] list = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
list[i] = dt.Rows[i]["TABLE_NAME"].ToString();
}
return list;
}
//连接
public OleDbConnection getcon(string strFileName)
{
String connString = string.Empty;
FileInfo file = new FileInfo(strFileName);
//connString = file.Extension == ".xlsx" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'" : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
connString = file.Extension == ".xlsx" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'" : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=Excel 8.0;";
return new OleDbConnection(connString);
}
#endregion
}
}