Spire.XLS for .NET

Spire.XLS for .NET 是一个独立的 Excel 组件,不依赖于 Microsoft Office Excel。它同时支持旧版的 Excel 97-2003(.xls) 格式文档和新版的 Excel 2007、Excel 2010、Excel 2013、Excel 2016(.xlsx, .xlsb, .xlsm) 以及 Open Office(.ods) 格式文档。

Spire.XLS for .NET 提供免费版和商业版,根据需要自行定夺,本站不给予任何授权和破解支持。官网:http://www.e-iceblue.cn/

以下是简单的 Execl 文件打印例子:

/// <summary>
/// 打印 Execl
/// </summary>
/// <param name="fileName">要打印的 Execl 文件</param>
/// <param name="documentName">打印机上显示的文件名称</param>
/// <param name="printerName">打印机全名</param>
/// <param name="paperName">纸张名称(默认:A4)</param>
/// <param name="landscape">是否横向打印(默认:false)</param>
/// <param name="copies">打印份数(默认:1)</param>
public static void PrintExecl(string fileName, string documentName, string printerName, string paperName = "A4", bool landscape = false, short copies = 1)
{
	using (Spire.Xls.Workbook workbook = new Spire.Xls.Workbook())
	{
		//加载要打印的execl
		workbook.LoadFromFile(fileName);
		//打印机全名
		workbook.PrintDocument.PrinterSettings.PrinterName = printerName;
		//打印机上显示的文件名称
		workbook.PrintDocument.DocumentName = documentName;
		//打印份数
		workbook.PrintDocument.PrinterSettings.Copies = copies;
		//设定纸张大小
		foreach (PaperSize pageSize in workbook.PrintDocument.PrinterSettings.PaperSizes)
		{
			if (pageSize.PaperName == paperName)
			{
				workbook.PrintDocument.PrinterSettings.DefaultPageSettings.PaperSize = pageSize;
			}
		}
		//是否横向打印
		workbook.PrintDocument.PrinterSettings.DefaultPageSettings.Landscape = landscape;
		//打印
		workbook.PrintDocument.Print();
	}
}

C# .NET 利用 HttpWebRequest 模拟浏览器 http、https 请求。

/// <summary>
/// Http 请求
/// </summary>
/// <param name="url">url地址</param>
/// <param name="data">请求参数(可选参数)</param>
/// <param name="method">请求方法(可选参数,默认:GET)</param>
/// <param name="timeout">连接超时(可选参数,默认: 100,000 毫秒(100 秒))</param>
/// <param name="encoding">编码(可选参数,默认:utf-8)</param>
/// <param name="contentType">Content-type HTTP 标头(可选参数)</param>
/// <param name="userAgent">模拟浏览器UA(可选参数)</param>
/// <param name="headers">HTTP 标头(可选参数)</param>
/// <returns>远程服务器返回的数据</returns>
public static string HttpRequest(string url, string data = null, string method = "GET", int? timeout = null, Encoding encoding = null, string contentType = "application/x-www-form-urlencoded", string userAgent = null, Dictionary<string, string> headers = null)
{
	encoding = encoding ?? Encoding.UTF8;
	var myWebRequest = (HttpWebRequest)WebRequest.Create(url);
	myWebRequest.ContentType = contentType;
	myWebRequest.Method = method;
	if (!string.IsNullOrWhiteSpace(userAgent))
	{
		myWebRequest.UserAgent = userAgent;
	}
	if (timeout.HasValue)
	{
		myWebRequest.Timeout = timeout.Value;
	}
	if (myWebRequest.RequestUri.Scheme == "https")
	{
		myWebRequest.ProtocolVersion = HttpVersion.Version11;
		myWebRequest.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CertificateValidationCallback);
		// 这里设置了协议类型。
		ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;// SecurityProtocolType.Tls1.2; 
		myWebRequest.KeepAlive = false;
		ServicePointManager.CheckCertificateRevocationList = true;
		ServicePointManager.DefaultConnectionLimit = 100;
		ServicePointManager.Expect100Continue = false;
	}
	if (headers != null && headers.Count > 0)
	{
		foreach (KeyValuePair<string, string> item in headers)
		{
			myWebRequest.Headers[item.Key] = item.Value;
		}
	}
	try
	{
		if (!string.IsNullOrWhiteSpace(data))
		{
			using (Stream stream = myWebRequest.GetRequestStream())
			{
				var bytes = encoding.GetBytes(data);
				stream.Write(bytes, 0, bytes.Length);
				stream.Flush();
			}
		}
		using (HttpWebResponse myWebResponse = (HttpWebResponse)myWebRequest.GetResponse())
		{
			if (myWebResponse.StatusCode == HttpStatusCode.OK)
			{
				using (Stream receiveStream = myWebResponse.GetResponseStream())
				{
					using (StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8))
					{
						return readStream.ReadToEnd();
					}
				}
			}
			else
			{
				throw new WebException(myWebResponse.StatusDescription);
			}
		}
	}
	catch (Exception ex)
	{
		throw ex;
	}
	finally
	{
		myWebRequest.Abort();
	}
}
/// <summary>
/// (SSL) 证书验证回调
/// </summary>
/// <param name="sender">一个对象,它包含此验证的状态信息。</param>
/// <param name="certificate">用于对远程方进行身份验证的证书。</param>
/// <param name="chain">与远程证书关联的证书颁发机构链。</param>
/// <param name="sslPolicyErrors">与远程证书关联的一个或多个错误。</param>
/// <returns>是否接受指定证书进行身份验证</returns>
private static bool CertificateValidationCallback(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
{
	if (sslPolicyErrors == SslPolicyErrors.None)
		return true;

	Console.WriteLine("Certificate error: {0}", sslPolicyErrors);
	return false;
}

IP地址转数字

public long IPToNumber(string ip)
{
	string[] arrayIP = ip.Split('.');
	long sip1 = long.Parse(arrayIP[0]);
	long sip2 = long.Parse(arrayIP[1]);
	long sip3 = long.Parse(arrayIP[2]);
	long sip4 = long.Parse(arrayIP[3]);
	long ipNum = sip1 * 256 * 256 * 256 + sip2 * 256 * 256 + sip3 * 256 + sip4;
	return ipNum;
}

数字转IP地址

public string NumberToIP(long ipNum)
{
	StringBuilder builder = new StringBuilder();
	builder.Append((ipNum >> 24) & 0xFF).Append(".");
	builder.Append((ipNum >> 16) & 0xFF).Append(".");
	builder.Append((ipNum >> 8) & 0xFF).Append(".");
	builder.Append(ipNum & 0xFF);
	return builder.ToString();
}

.NET 版 Chromium WebBrowser 动态设置 http, https, socks5 代理方法。

private void InitWebBrowser(string url)
{
	ChromiumWebBrowser browser = new ChromiumWebBrowser(url);
	browser.Dock = DockStyle.Fill;
	//绑定 WebBrowser 初始化完成事件
	browser.IsBrowserInitializedChanged += Browser_IsBrowserInitializedChanged;
	//添加到WinForm中
	this.Controls.Add(browser);
	browser.BringToFront();
}

private void Browser_IsBrowserInitializedChanged(object sender, IsBrowserInitializedChangedEventArgs e)
{
	if(e.IsBrowserInitialized)
	{
		//只能在WebBrowser UI呈现后获取 Request 上下文
		Cef.UIThreadTaskFactory.StartNew(delegate
		{
			//获取 Request 上下文
			var rc = browser.GetBrowser().GetHost().RequestContext;
			var dict = new Dictionary<string, object>();
			dict.Add("mode", "fixed_servers");
			dict.Add("server", "127.0.0.1:1080");
			string error;
			//设置代理
			rc.SetPreference("proxy", dict, out error);
			//如果 error 不为空则表示设置失败。
			if(!string.IsNullOrWhiteSpace(error))
			{
				MessageBox.Show(error, "Tip", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		});
	}
}

不要在 Cef.Initialize 时设置 CefCommandLineArgs.Add("proxy-server", "127.0.0.1:1080");,否则修改代理设置会失败。

利用 shell32 API清除IE缓存。好处是调用系统原生清理,保证效果,不好的就是会弹出窗口,影响用户体验。

public class ShellUtil
{
	[DllImport("shell32.dll")]
	public static extern IntPtr ShellExecute(IntPtr hwnd, string lpOperation, string lpFile, string lpParameters, string lpDirectory, ShowCommands nShowCmd);
}

public enum ShowCommands : int
{
	SW_HIDE = 0,
	SW_SHOWNORMAL = 1,
	SW_NORMAL = 1,
	SW_SHOWMINIMIZED = 2,
	SW_SHOWMAXIMIZED = 3,
	SW_MAXIMIZE = 3,
	SW_SHOWNOACTIVATE = 4,
	SW_SHOW = 5,
	SW_MINIMIZE = 6,
	SW_SHOWMINNOACTIVE = 7,
	SW_SHOWNA = 8,
	SW_RESTORE = 9,
	SW_SHOWDEFAULT = 10,
	SW_FORCEMINIMIZE = 11,
	SW_MAX = 11
}

1、 清除IE临时文件

ShellExecute(IntPtr.Zero, "open", "rundll32.exe", " InetCpl.cpl,ClearMyTracksByProcess 255", "", ShowCommands.SW_HIDE);

2、清除IE Cookies

ShellUtil.ShellExecute(IntPtr.Zero, "open", "rundll32.exe", " InetCpl.cpl,ClearMyTracksByProcess 2", "", ShowCommands.SW_HIDE);

3、清除IE History

ShellUtil.ShellExecute(IntPtr.Zero, "open", "rundll32.exe", " InetCpl.cpl,ClearMyTracksByProcess 1", "", ShowCommands.SW_HIDE);

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace utility
{
    /// <summary>
    /// Xml序列化与反序列化
    /// </summary>
    public class XmlUtil
    {
        /// <summary>
        /// 反序列化
        /// </summary>
        /// <param name="xml">XML字符串</param>
        /// <returns></returns>
        public static T Deserialize<T>(string xml) where T : new()
        {
            try
            {
                using (Stream sr = new FileStream(xml, FileMode.Open))
                {
                    XmlSerializer xmldes = new XmlSerializer(typeof(T));
                    return (T)xmldes.Deserialize(sr);
                }
            }
            catch
            {
                return default(T);
            }
        }
		
        /// <summary>
        /// 序列化
        /// </summary>
        /// <param name="obj">对象</param>
        /// <returns></returns>
        public static string Serializer<T>(T obj)
        {
            using (MemoryStream Stream = new MemoryStream())
            {
                XmlSerializer xml = new XmlSerializer(typeof(T));
                try
                {
                    //序列化对象
                    xml.Serialize(Stream, obj);
                }
                catch (InvalidOperationException)
                {
                    throw;
                }
                Stream.Position = 0;
                using (StreamReader sr = new StreamReader(Stream))
                {
                    return sr.ReadToEnd();
                }
            }
        }
    }
}

//源Execl名称
string sourceExeclFileName = "C:\\SourceExecl.xlsx";
//目标Execl名称
string targetExeclFileName = "C:\\TargetExecl.xlsx";
using (ExcelPackage package = new ExcelPackage(new FileInfo(targetExeclFileName)))
{
	using (ExcelPackage sourcePackage = new ExcelPackage(new FileInfo(sourceExeclFileName)))
	{
		//new sheet form copy为复制到目标Execl的SheetName
		package.Workbook.Worksheets.Add("new sheet from copy", sourcePackage.Workbook.Worksheets[1]);
	}
	package.Save();
}

/// <summary>
/// 获取日期是第几周
/// </summary>
/// <param name="date">日期</param>
/// <returns>第几周</returns>
public static int GetWeekOfYear(DateTime date)
{
	//确定此时间在一年中的位置
	int dayOfYear = date.DayOfYear;
	//当年第一天
	DateTime tempDate = new DateTime(date.Year, 1, 1);
	//确定当年第一天
	int tempDayOfWeek = (int)tempDate.DayOfWeek;
	tempDayOfWeek = tempDayOfWeek == 0 ? 7 : tempDayOfWeek;
	//确定星期几
	int index = (int)date.DayOfWeek;
	index = index == 0 ? 7 : index;
	//当前周的范围
	DateTime retStartDay = date.AddDays(-(index - 1));
	DateTime retEndDay = date.AddDays(7 - index);
	//确定当前是第几周
	int weekOfYear = (int)Math.Ceiling(((double)dayOfYear + tempDayOfWeek - 1) / 7);
	if (retStartDay.Year < retEndDay.Year)
	{
		weekOfYear = 1;
	}
	return weekOfYear;
}

默认输出的 InnerXml 显示在一行,看起来非常痛苦,特别是复杂XML文档,输出格式化后的 InnerXml 可以方便快速的查看。

public string GetFormattedInnerXml(XmlDocument doc)
{
    StringBuilder builder = new StringBuilder();
    using (StringWriter sw = new StringWriter(builder))
    {
        using (XmlTextWriter xtw = new XmlTextWriter(sw))
        {
            xtw.Formatting = Formatting.Indented;
            xtw.Indentation = 1;
            xtw.IndentChar = '\t';
            doc.WriteTo(xtw);
        }
    }
    return builder.ToString();
}