private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.Title = "请选择文件";
fileDialog.Filter = "excel03文件(*.xls)|*.xls";
fileDialog.FilterIndex = 1;
fileDialog.RestoreDirectory = true;
if (fileDialog.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application xlsapp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlswkbk = default(Microsoft.Office.Interop.Excel.Workbook);
xlswkbk = xlsapp.Workbooks.Open(fileDialog.FileName, Missing.Value,false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet xlsst = default(Microsoft.Office.Interop.Excel.Worksheet);
for (int i = 1; i <= xlswkbk.Sheets.Count;i++ )
{
xlsst = (Microsoft.Office.Interop.Excel.Worksheet)xlswkbk.Sheets[i];
int intLastColNum = xlsst.UsedRange.Columns.Count;
int intLastRowNum = xlsst.UsedRange.Rows.Count;
for (int j = 2; j <= intLastRowNum; j++)
{
//获取第一列数据
string fstMan = ((Microsoft.Office.Interop.Excel.Range)xlsst.Cells[j, 1]).Text.ToString();
//string scdMan = ((Microsoft.Office.Interop.Excel.Range)xlsst.Cells[j, 31]).Text.ToString();
StrFomat(fstMan, xlsst, j, 1);
//StrFomat(scdMan, xlsst, j,31);
}
}
xlswkbk.Save();
MessageBox.Show("格式化成功！");
xlswkbk.Close(null, null, null);
xlsapp.Workbooks.Close();
xlsapp.Application.Quit();
xlsapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsst);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlswkbk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsapp);
Kill(xlsapp);
GC.Collect();//强行销毁
}
/// <summary>
/// 字符串格式化
/// </summary>
/// <param name="contctMan">初始字符串</param>
/// <param name="xlsst">工作sheet</param>
/// <param name="row">行</param>
/// <param name="col">列</param>
private void StrFomat(string contctMan, Worksheet xlsst,int row,int col)
{
if (string.IsNullOrEmpty(contctMan) == false)
{
Match result = Regex.Match(contctMan, @"[\u4e00-\u9fa5]+", RegexOptions.IgnoreCase | RegexOptions.Multiline);
if (result.Success)
{
string name = result.Value;
contctMan = contctMan.Replace(name, "").ToString().Trim();
if (contctMan.Length > 0)
{
//根据一个或多个空格分割字符串
string[] contctWay = Regex.Split(contctMan, "\\s{1,}");
string tel = "";
string phone = "";
Regex regphone = new Regex(@"^1[3458]\d{9}$", RegexOptions.IgnoreCase);
switch (contctWay.Length)
{
case 1:
if (contctWay[0].Contains('-') == false && regphone.IsMatch(contctWay[0].ToString()))
{
phone = contctWay[0].ToString();
}
else
{
tel = contctWay[0].ToString();
}
break;
case 2:
if (contctWay[0].Contains('-') == false && regphone.IsMatch(contctWay[0].ToString()))
{
phone = contctWay[0].ToString();
tel = contctWay[1].ToString();
}
else
{
tel = contctWay[0].ToString();
phone = contctWay[1].ToString();
}
break;
}
xlsst.Cells[row, col] = name;
xlsst.Cells[row, col + 1] = tel;
xlsst.Cells[row, col + 2] = phone;
}
}
else
{
contctMan = "无" + contctMan;
StrFomat(contctMan, xlsst, row, col);
}
}
}
/// <summary>
/// 杀死当前格式化调用的Excel进程
/// </summary>
/// <param name="hwnd"></param>
/// <param name="ID"></param>
/// <returns></returns>
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
int k = 0;
GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
p.Kill(); //关闭进程k
}