网站自动化录入小工具
公司要录入一大批信息到OA 系统,破网站也是年久失修没有批量导入操作。
只能人手工一条条对着EXCEL导入,这啥时候到头啊,要是用按键精灵的话,
电脑就一直不能关,不能动,还有一大堆活呢,索性写个小工具。
第一步:解析Excle 转化成实体类
为了保证结果没有错误,可以自我检查,可以把数据放到一个table里面;
下面是解析excel的代码*:(**注意下xls 和 xlsx 两种格式,引用NPOI即可**)
转化实体类的就不写了,就是遍历返回List 然后给实体赋值)
/// <summary>
/// Excel转化到DataTable
/// </summary>
/// <param name="strFileName">文件路径</param>
/// <returns>true:成功 false:失败</returns>
private List<string> ExcelToDataTable(string strFileName)
{
List<string> list = new List<string>();
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
hssfworkbook = new HSSFWorkbook(file);
else if (fileExt == ".xlsx")
xssfworkbook = new XSSFWorkbook(file);//初始化太慢了,不知道这是什么bug
}
if (hssfworkbook != null)
{
list.Clear();
int count = hssfworkbook.NumberOfSheets; //获取所有SheetName
for (int sheetnum = 0; sheetnum < count;sheetnum++ )
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetnum);
if (sheet != null)
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
list.Add(row.GetCell(j).ToString());
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
dataGridView1.DataSource = dt;
}
}
}
else if (xssfworkbook != null)
{
int isFrist = 0;
int cellCount = 0;
int count1 = xssfworkbook.NumberOfSheets; //获取所有SheetName
for (int sheetnum = 0; sheetnum < count1; sheetnum++)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetnum);
if (xSheet != null)
{
if (isFrist==0)
{
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
isFrist++;
}
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
list.Add(row.GetCell(j).ToString());
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
dataGridView1.DataSource = dt;
}
}
}
return list;
}
第二步:开始使用抓包工具,我用的wireshark,设置好ip和通讯方式以及端口,把你想要自动化的步骤操作一次,记录下包内容然后开始拼凑string
eg:登录时候,我抓到的包 j_username=123 j_password=123
那我就拼好String 发送到访问连接就好了;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("j_username={0}&j_password={1}", name, pwd);
第三步:发包(最后根据返回信息判断是否成功)
/// </summary>
/// <param name="referUrl"> 目标地址</param>
/// <param name="data">发送信息</param>
/// <returns>返回信息</returns>
public string PostLogin(string referUrl, string data)
{
CookieContainer cookie = new CookieContainer();
string result = "";
try
{
//命名空间System.Net下的HttpWebRequest类
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(referUrl);
//参照浏览器的请求报文 封装需要的参数 这里参照ie9
//浏览器可接受的MIME类型
request.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8";
//包含一个URL,用户从该URL代表的页面出发访问当前请求的页面
request.Referer = referUrl;
//浏览器类型,如果Servlet返回的内容与浏览器类型有关则该值非常有用
request.UserAgent = "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36";
// request.UserAgent = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)";
request.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
//请求方式
request.Method = "POST";
//是否保持常连接
request.KeepAlive = true;
request.Headers.Add("Accept-Encoding", "gzip, deflate");
//表示请求消息正文的长度
request.ContentLength = data.Length;
Stream postStream = request.GetRequestStream();
request.CookieContainer = new CookieContainer();
byte[] postData = Encoding.UTF8.GetBytes(data);
//将传输的数据,请求正文写入请求流
postStream.Write(postData, 0, postData.Length);
postStream.Dispose();
//响应
string StrDate = "";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
cookie.Add(response.Cookies);
MessageBox.Show("获取到cookies" + response.Cookies);
Stream str = response.GetResponseStream();
StreamReader Reader = new StreamReader(str, Encoding.Default);
while ((StrDate = Reader.ReadLine()) != null)
{
result += StrDate + "\r\n";
}
// 判断响应的信息是否为压缩信息 若为压缩信息解压后返回
if (response.ContentEncoding == "gzip")
{
MemoryStream ms = new MemoryStream();
GZipStream zip = new GZipStream(response.GetResponseStream(), CompressionMode.Decompress);
byte[] buffer = new byte[1024];
int l = zip.Read(buffer, 0, buffer.Length);
while (l > 0)
{
ms.Write(buffer, 0, l);
l = zip.Read(buffer, 0, buffer.Length);
}
ms.Dispose();
zip.Dispose();
result = Encoding.UTF8.GetString(ms.ToArray());
}
return result;
}
catch (Exception)
{
throw;
}
}