Допустим, вам надо перенести определенные данные с какого-то сайта в свою базу данных в красивом, структурированном виде. Но вы не хотите сидеть и копи-пастить в Excel эти данные. Тогда может помочь следующий метод. Он основан на использовании C# приложения для загрузки, очистки и парсинга HTML и дальнейшей загрузке данных в Excel программно.
Для пользования данным методом нужно владеть языком C#, так как мой код надо всегда затачивать под каждый сайт.
1. Открываем среду разработки на C#(Я пользуюсь MS Visual Studio 2010 Ultimate). Создаем новый проект с простой формой:
В адресе URL мы введем страницу, из которой нужно получить контент при нажатии на кнопку. При этом автоматически откроется Excel 2007 и в него занесутся данные – этот контент.
2. Нажимаем правой кнопкой по References под названием проекта в Solution Explorer. Выбираем Add Reference. Далее во вкладке COM находим и выбираем компоненту Microsoft Excel 12.0 Object Library. Ссылки на эту компоненту понадобятся для автоматической загрузки данных в Excel.
3. Редактируем список пространств имен:
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.Linq;
using System.Web;
using System.IO;
using System.Net;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
4. Пишем код для кнопки.
private void button1_Click(object sender, EventArgs e)
{
string OldText, NewText, PossibleRefText, RefText, textOfCitation, authorOfCitation;
strURL = textBox1.Text;
OldText = GetHtmlFromUrl(strURL);
int begin, begin0, i;
int end;
int length;
int lastindex;
int newindex;
int indexOfClosingTag, difference, indexOfHref;
int indexofrefbegin, indexofspanbegin, indexOfAuthorEnd, indexOfAuthorBegin, indexOfAuthorBegin0;
RefText = "<a href";
newindex = OldText.IndexOf("<p class=\"data\">");
begin = OldText.IndexOf("<p class=\"data\">");
lastindex = OldText.LastIndexOf("<p class=\"data\">");
OldText = OldText.Remove(0, newindex);
i = 0;
while (!(newindex == (-1)))
{
i = i + 1;
begin0 = OldText.IndexOf("<p class=\"data\">");
begin = OldText.IndexOf("<p class=\"data\">") + 16;
indexofrefbegin = begin;
end = OldText.IndexOf("</p>");
length = end - begin;
if (length < 0)
{
break;
}
textOfCitation = OldText.Substring(begin, length);
OldText = OldText.Remove(0, end + 4);
indexOfAuthorBegin0 = OldText.IndexOf("class=\"author\"");
OldText = OldText.Remove(0, indexOfAuthorBegin0);
indexOfAuthorBegin0 = OldText.IndexOf("class=\"author\"");
indexOfHref = OldText.IndexOf("href=");
difference = indexOfHref - (indexOfAuthorBegin0 + 15);
if (difference < 5 && difference >= 0)
{
indexOfClosingTag = OldText.IndexOf(">");
length = indexOfClosingTag - indexOfAuthorBegin0 - 14;
OldText = OldText.Remove(indexOfAuthorBegin0 + 15, length);
}
indexOfAuthorBegin = OldText.IndexOf("class=\"author\"") + 15;
indexOfAuthorEnd = OldText.IndexOf("<");
length = indexOfAuthorEnd - indexOfAuthorBegin;
if (length < 0)
{
break;
}
authorOfCitation = OldText.Substring(indexOfAuthorBegin, length);
OldText = OldText.Remove(0, indexOfAuthorEnd + 1);
//Literal1.Text = Literal1.Text + " " + i.ToString() + ". " + textOfCitation + " " + "(" + authorOfCitation + ")" + "</br>";
textOfCitation = cleanFromBr(textOfCitation);
textOfCitation = cleanFromQuot(textOfCitation);
citations[i - 1, 0] = textOfCitation;
citations[i - 1, 1] = authorOfCitation;
}
//Добавляем массив цитат в Excel
InsertToExcel();
// движок для википедии
//{
// string OldText, NewText, PossibleRefText, RefText;
// strURL = TextBox1.Text;
// OldText = GetHtmlFromUrl(strURL);
// int begin;
// int end;
// int length;
// int lastindex;
// int newindex;
// int indexofrefbegin, indexofspanbegin;
// RefText = "<p class=\"data\">";
// newindex = OldText.IndexOf(RefText);
// begin = OldText.IndexOf(RefText);
// lastindex = OldText.LastIndexOf("</p>");
// while (!(newindex == (-1)))
// {
// begin = OldText.IndexOf(RefText) + 16;
// end = OldText.IndexOf("</p>");
// length = end - begin;
// NewText = OldText.Substring(begin, length);
// newindex = OldText.IndexOf(RefText);
// Literal1.Text = Literal1.Text + NewText;
// }
//}
}
Здесь мы сначала запрашиваем HTML в текстовую строку с помощью функции GetHtmlFromUrl. Затем мы эту текстовую строку обрабатываем, удаляя все ненужное, а нужное занося в массив. Затем добавляем получившийся массив в Excel с помощью функции InsertToExcel().
5. Пишем дополнительный вспомогательный код с функциями, которые использовались в коде для кнопки:
private string cleanFromBr(string text)
{
int indexOfStart, indexOfEnd;
indexOfStart = text.IndexOf("<br/>") - 1;
while (indexOfStart != -2)
{
indexOfEnd = indexOfStart + 6;
text = text.Remove(indexOfStart, 6);
text = text.Insert(indexOfStart, " ");
indexOfStart = text.IndexOf("<br/>") - 1;
}
return text;
}
private string cleanFromQuot(string text)
{
int indexOfStart, indexOfEnd;
indexOfStart = text.IndexOf(""");
while (indexOfStart != -1)
{
indexOfEnd = indexOfStart + 6;
text = text.Remove(indexOfStart, 6);
text = text.Insert(indexOfStart, "\"");
indexOfStart = text.IndexOf(""");
}
return text;
}
private void InsertToExcel()
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
//oSheet.Cells[1, 1] = "First Name";
//oSheet.Cells[1, 2] = "Last Name";
//oSheet.Cells[1, 3] = "Full Name";
//oSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
//oSheet.get_Range("A1", "D1").Font.Bold = true;
//oSheet.get_Range("A1", "D1").VerticalAlignment =
// Excel.XlVAlign.xlVAlignCenter;
//Fill A1:B20 with an array of values (First and Last Names).
oSheet.get_Range("B1", "C20").Value2 = citations;
////Fill C2:C6 with a relative formula (=A2 & " " & B2).
//oRng = oSheet.get_Range("C2", "C6");
//oRng.Formula = "=A2 & \" \" & B2";
////Fill D2:D6 with a formula(=RAND()*100000) and apply format.
//oRng = oSheet.get_Range("D2", "D6");
//oRng.Formula = "=RAND()*100000";
//oRng.NumberFormat = "$0.00";
////AutoFit columns A:D.
//oRng = oSheet.get_Range("A1", "D1");
//oRng.EntireColumn.AutoFit();
//Manipulate a variable number of columns for Quarterly Sales Data.
//DisplayQuarterlySales(oSheet);
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
//MessageBox.Show(errorMessage, "Error");
}
}
public enum ResponseCategories
{
Unknown = 0, // Unknown code ( < 100 or > 599)
Informational = 1, // Informational codes (100 >= 199)
Success = 2, // Success codes (200 >= 299)
Redirected = 3, // Redirection code (300 >= 399)
ClientError = 4, // Client error code (400 >= 499)
ServerError = 5 // Server error code (500 >= 599)
}
public static string GetHtmlFromUrl(string url)
{
if (string.IsNullOrEmpty(url))
throw new ArgumentNullException("url", "Parameter is null or empty");
string html = "";
HttpWebRequest request = GenerateHttpWebRequest(url);
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
if (VerifyResponse(response) == ResponseCategories.Success)
{
// Get the response stream.
Stream responseStream = response.GetResponseStream();
// Use a stream reader that understands UTF8.
using (StreamReader reader =
new StreamReader(responseStream, Encoding.UTF8))
{
html = reader.ReadToEnd();
}
}
}
return html;
}
public static HttpWebRequest GenerateHttpWebRequest(string UriString)
{
// Get a Uri object.
Uri Uri = new Uri(UriString);
// Create the initial request.
HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(Uri);
// Return the request.
return httpRequest;
}
// POST overload
public static HttpWebRequest GenerateHttpWebRequest(string UriString,
string postData,
string contentType)
{
// Get a Uri object.
Uri Uri = new Uri(UriString);
// Create the initial request.
HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(Uri);
// Get the bytes for the request; should be pre-escaped.
byte[] bytes = Encoding.UTF8.GetBytes(postData);
// Set the content type of the data being posted.
httpRequest.ContentType = contentType;
//"application/x-www-form-urlencoded"; for forms
// Set the content length of the string being posted.
httpRequest.ContentLength = postData.Length;
// Get the request stream and write the post data in.
using (Stream requestStream = httpRequest.GetRequestStream())
{
requestStream.Write(bytes, 0, bytes.Length);
}
// Return the request.
return httpRequest;
}
public static ResponseCategories VerifyResponse(HttpWebResponse httpResponse)
{
// Just in case there are more success codes defined in the future
// by HttpStatusCode, we will check here for the "success" ranges
// instead of using the HttpStatusCode enum as it overloads some
// values.
int statusCode = (int)httpResponse.StatusCode;
if ((statusCode >= 100) && (statusCode <= 199))
{
return ResponseCategories.Informational;
}
else if ((statusCode >= 200) && (statusCode <= 299))
{
return ResponseCategories.Success;
}
else if ((statusCode >= 300) && (statusCode <= 399))
{
return ResponseCategories.Redirected;
}
else if ((statusCode >= 400) && (statusCode <= 499))
{
return ResponseCategories.ClientError;
}
else if ((statusCode >= 500) && (statusCode <= 599))
{
return ResponseCategories.ServerError;
}
return ResponseCategories.Unknown;
}
Часть этих функций нужна, чтобы запросить HTML, часть чтобы очистить его от тэгов. Набор этих функций, как и код кнопки будут меняться в зависимости от того, с какого именно сайта вы хотите получить контент. То есть данный метод не универсален, его надо затачивать под каждый сайт. Я не буду подробно разъяснять этот код, так как все равно нужно обладать хорошими знаниями в C#, чтобы применять данный метод. Кроме этого, тут есть неплохие комментарии.
4. Здесь http://support.microsoft.com/kb/302084 описано, как писать код для загрузки данных в Excel.
5. Далее, как я описал ранее, можно выгрузить данные из Excel в XML, а XML уже загрузить туда, куда вам надо. Смотрите предыдущий пост в этом блоге.
