分类： 编程

C#使用NPOI导出Excel

C#使用NPOI导出Excel

使用代码如下：

using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using System.Text;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;

namespace Common
{
    public class NPOIHelper
    {
        public void Export(System.Windows.Forms. ListView listView, string fileName)
        {
            var hssfworkbook = new HSSFWorkbook();
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory .CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;



            //调整列宽
            var sheet1 = hssfworkbook.CreateSheet( "Sheet1");
            sheet1.SetColumnWidth(0, 10000);
            sheet1.SetColumnWidth(1, 10000);
            sheet1.SetColumnWidth(2, 10000);
            sheet1.SetColumnWidth(3, 10000);

            //表头
            var row0 = sheet1.CreateRow(0);
            row0.CreateCell(0).SetCellValue( "企业名称" );
            row0.CreateCell(1).SetCellValue( "中文字号" );
            row0.CreateCell(2).SetCellValue( "中文拼音" );
            row0.CreateCell(3).SetCellValue( "行业特征" );

            for( int i=0; i<listView.Items.Count; i++)
            {
                string a = listView.Items[i].SubItems[0].Text;
                string b = listView.Items[i].SubItems[1].Text;
                string c = listView.Items[i].SubItems[2].Text;
                string d = listView.Items[i].SubItems[3].Text;
                var row = sheet1.CreateRow(i+1); //如果不使用表头，这里就不用+1了
                row.CreateCell(0).SetCellValue(a);
                row.CreateCell(1).SetCellValue(b);
                row.CreateCell(2).SetCellValue(c);
                row.CreateCell(3).SetCellValue(d);
            }


            //Write the stream data of workbook to the root directory
            //MemoryStream file = new MemoryStream();
           // hssfworkbook.Write(file);

            //写入文件
            FileStream file = new FileStream (fileName, FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
     

        }
    }
}

调用：

注意先using Common;

/*保存对话框*/
SaveFileDialog saveFileDialog = new SaveFileDialog ();
saveFileDialog.Filter = "导出Excel(*.xls)|*.xls";

if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
    NPOIHelper npoi = new NPOIHelper();
    npoi.Export(this.listView1, saveFileDialog.FileName);
    MessageBox.Show( "导出完成！" );
              
}

C# Http GET 和POST HttpClient HttpHelper

C# Http GET 和POST HttpClient HttpHelper

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
namespace alimama
{
    class HttpHelper
    {
        private static CookieContainer cookieContainer = new CookieContainer();
        private static int _timeOut = 1000 * 30; // 30秒超时  默认值是 100,000 毫秒（100 秒）
        static public string HttpGet(string url, string referer = "")
        {
            HttpWebRequest httpWebRequest = null;
            HttpWebResponse httpWebResponse = null;
            httpWebRequest = (HttpWebRequest)HttpWebRequest.Create(url);
            httpWebRequest.CookieContainer = cookieContainer;
            httpWebRequest.Method = "GET";
            httpWebRequest.ServicePoint.ConnectionLimit = int.MaxValue;
            if (!string.IsNullOrEmpty(referer))
            {
                httpWebRequest.Referer = referer;
            }
            //httpWebRequest.Host = Host;
            httpWebRequest.UserAgent = "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.63 Safari/537.36";
            httpWebRequest.KeepAlive = true;
            httpWebRequest.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8";
            httpWebRequest.ServicePoint.Expect100Continue = false;
            httpWebRequest.Timeout = _timeOut; //默认值是 100,000 毫秒（100 秒）
            httpWebResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            Stream responseStream = httpWebResponse.GetResponseStream();
            StreamReader streamReader = new StreamReader(responseStream, Encoding.UTF8);
            string html = streamReader.ReadToEnd();
            streamReader.Close();
            responseStream.Close();
            httpWebRequest.Abort();
            httpWebResponse.Close();
            return html;
        }
        static public string HttpPost(string url, string postData, string referer = "")
        {
            HttpWebRequest httpWebRequest = null;
            HttpWebResponse httpWebResponse = null;
            httpWebRequest = (HttpWebRequest)HttpWebRequest.Create(url);
            httpWebRequest.CookieContainer = cookieContainer;
            httpWebRequest.Method = "POST";
            httpWebRequest.ServicePoint.ConnectionLimit = int.MaxValue;
            if (!string.IsNullOrEmpty(referer))
            {
                httpWebRequest.Referer = referer;
            }
            //httpWebRequest.Host = Host;
            httpWebRequest.UserAgent = "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.63 Safari/537.36";
            httpWebRequest.KeepAlive = true;
            httpWebRequest.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
            httpWebRequest.ServicePoint.Expect100Continue = false;
            httpWebRequest.Timeout = _timeOut; //默认值是 100,000 毫秒（100 秒）
            byte[] byteArray = Encoding.UTF8.GetBytes(postData); //转化
            httpWebRequest.ContentLength = byteArray.Length;
            Stream newStream = httpWebRequest.GetRequestStream();
            newStream.Write(byteArray, 0, byteArray.Length); //写入参数
            newStream.Close();
            httpWebResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            Stream responseStream = httpWebResponse.GetResponseStream();
            StreamReader streamReader = new StreamReader(responseStream, Encoding.UTF8);
            string html = streamReader.ReadToEnd();
            streamReader.Close();
            responseStream.Close();
            httpWebRequest.Abort();
            httpWebResponse.Close();
            return html;
        }
        static public void Download(string url, string savePath)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
            request.CookieContainer = cookieContainer;
            WebResponse response = request.GetResponse();
            Stream reader = response.GetResponseStream();
            FileStream writer = new FileStream(savePath, FileMode.OpenOrCreate, FileAccess.Write);
            byte[] buff = new byte[512];
            int c = 0; //实际读取的字节数
            while ((c = reader.Read(buff, 0, buff.Length)) > 0)
            {
                writer.Write(buff, 0, c);
            }
            writer.Close();
            writer.Dispose();
            reader.Close();
            reader.Dispose();
            response.Close();
        }
    }
}

.NET采集用到的包

ScrapySharp + HtmlAgilityPack

这两个库基本可以完成98%的采集需求，剩下的2%可以再加一个库: phantomjs

目前接到的需求中，还没有使用到 phantomjs这个库。

补充（2015-3-9）：

今天又发现了几个和采集网页相关的库（以下4个都没用过，先记下来）

  1. CsQuery。CsQuery可以算是.net中实现的Jquery, 可以使用类似Jquery中的方法来处理html页面。CsQuery的项目地址是https://github.com/afeiship/CsQuery
  2. AngleSharp （据说有内存泄漏，不知道现在还有没有）
  3. fizzler   fizzler是HtmlAgilityPack的一个扩展，支持jQuery Selector
  4. NSoup   NSoup是JSoup的Net移植版本。

ScrapySharp + HtmlAgilityPack 基本用法举例：

string htmlstr = HttpHelper.HttpPost(“http://www.tzgsj.gov.cn/baweb/show/shiju/queryByName.jsp” , “spellcondition=%E9%BE%99” );

HtmlDocument doc = new HtmlDocument ();

doc.LoadHtml(htmlstr);

HtmlNode docNode = doc.DocumentNode;

var nodes = docNode.CssSelect(“#content” );

这样就拿到了所有ID为content的DOM元素。

这段HTML如下：

为了进一步拿到里面的td的文字，可以接着这样写：

foreach(var node in nodes)
{
    var tdNodes = node.CssSelect( "td");
    foreach(var td in tdNodes)
    {
        string text = td.InnerText;
    }
}

经过这个双重循环后，所有的td里的内容就都被采集出来了。

总结，ScrapySharp 主要是这个CssSelect好用。它是HtmlAgilityPack的一个扩展

EnumWindows遍历窗口

EnumWindows遍历窗口

#include <vector>
using namespace std;


vector<HWND > m_hWnds;
BOOL EnumWindowsProc(HWND hWnd , LPARAM lParam )
{
    if (::GetWindowLong( hWnd, GWL_STYLE)& WS_VISIBLE)
    {
        char szClassName[128] = { 0 };
        if (::GetClassNameA( hWnd, szClassName, 128) != 0)
        {
            if (strcmp( "3DSMAX", szClassName) == 0)
            {
                m_hWnds.push_back( hWnd);
            }
        }

    }
    return TRUE;
}

int Get3dmaxWndCount()
{
    m_hWnds.clear();
    ::EnumWindows((WNDENUMPROC)EnumWindowsProc, NULL);

    return (int)m_hWnds.size();
}

IIS上跑PHP，遭遇上传权限问题

其实这个问题不关IIS以及PHP什么事，是我的权限没设置好。

在使用PHP上传文件时，一直报错，错误信息如下：

PHP Warning: move_uploaded_file(): Unable to move ‘C:\Windows\Temp…

一开始以为是C:\Windows\Temp的权限问题，结果加上IUSR，并给予所有权限后，还是不行。

然后又给uploads文件夹给IUSR账户所有权限，结果还是不行。

最后在uploads文件上给IIS_IUSERS账户加上所有权限后，问题解决。

总结：

问题的主要原因是我没有弄清楚IIS的两个账户分别是什么意思，关于IUSR和IIS_IUSER的区别。

简单查了一下，知道 IUSR is part of IIS_IUSER group

基本的PHP上传文件

基本的PHP上传文件

表单：

<form id ="form1" method="POST" enctype="multipart/form-data" >
<input name ="room_logo" type="file"  value="" id ="room_logo" class="input normal"   sucmsg=" " />
</form>

注意：表单上要加 enctype=”multipart/form-data” 

PHP处理代码：

<?php
$method = $_SERVER['REQUEST_METHOD' ];
if ($method == 'POST') { 
    $destination_folder ="../../uploads/"; //上传文件路径
    //是否存在文件
    if ( is_uploaded_file($_FILES ["room_logo"]["tmp_name" ])) {       
        $file = $_FILES["room_logo" ];
        if(!file_exists( $destination_folder )) {
            mkdir ($destination_folder);
        }
        $filename=$file["tmp_name"];
        $pinfo=pathinfo( $file["name" ]);
        $ftype=$pinfo['extension'];
        $destination = $destination_folder.time(). ".".$ftype ;
        if(!move_uploaded_file ($filename, $destination)){
            echo " 移动文件出错 ";
        }              
    }
}

?>

mysql常用语句

mysql常用语句

--日期格式化
DATE_FORMAT(genTime , '%Y-%m-%d' )
--获取当天日期的数据
SELECT * FROM newphone WHERE DATE_FORMAT(postdate , '%Y-%m-%d' )= CURDATE()


--查询mysql数据库中所有表名
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名'

--查看mysql是否区分表名大小写
show variables where Variable_name like '%lower_case_table_names%'

--修改表名区分大小写的配置:修改MySql的配置文件，在mysqld节下加入下面一行 set-variable=lower_case_table_names=0（0：大小写敏感；1：大小写不敏感）最后重启一下MySql服务即可。 


--修改表名
ALTER TABLE old_table_name RENAME TO new_table_name

WIN32 主线程中不能使用 WaitForSingleObject

主线程中不能使用 WaitForSingleObject(m_GameProcessInfo.hProcess, INFINITE);

这样会造成莫名奇妙的问题，比如我在一个项目这样用了，结果客户反馈说程序会造成鼠标一在转圈圈。经远程远程客户机排查，结果就是在主线程中使用WaitForSingleObject(m_GameProcessInfo.hProcess, INFINITE); 导致了主线程消息循环阻塞而引发的。

经过在网上查找，很快找到了解决方案,用下面的代码取代WaitForSingleObject.

while (TRUE)
{
    DWORD result;
    MSG msg;

    result = MsgWaitForMultipleObjects(1, &m_GameProcessInfo.hProcess,
               FALSE, INFINITE, QS_ALLINPUT);

    if (result == ( WAIT_OBJECT_0))
    {
        break;
    }
    else
    {
        PeekMessage(&msg, NULL , 0, 0, PM_REMOVE );
        DispatchMessage(&msg);
    }
}

微软对此函数的解释是:

为了解决在主线程中Wait的问题，微软专门设计了一个函数MsgWaitForMultipleObjects，这个函数即可以等待信号（thread,event,mutex等等），也可以等待消息（MSG）。即不论有信号被激发或者有消息到来，此函数都可以返回。

这样改过后，再发给客户测试，现象消失，问题解决。

WIN32获取当前程序所在目录

WIN32获取当前程序所在目录

#include <Shlwapi.h>
#pragma comment (lib , "shlwapi.lib" )

TCHAR wszModulePath[MAX_PATH ];
::memset(wszModulePath, 0, sizeof(TCHAR)*MAX_PATH);
GetModuleFileName(NULL , wszModulePath, MAX_PATH);
PathAppend(wszModulePath, _T ("..\\" ));
wcscat_s(wszModulePath, GAME_CLIENT_PATH);