利用NPOI操作excel导出

来源：互联网发布：守望先锋安娜技能数据编辑：程序博客网时间：2024/05/22 14:40

npoi插件要.netframe4.0

打开excel按钮事件

private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog fileDialog1 = new OpenFileDialog();
fileDialog1.InitialDirectory = Environment.CurrentDirectory;
fileDialog1.Filter = "XLS files (*.xls)|*.xls|All files (*.*)|*.*";
fileDialog1.FilterIndex = 1;
fileDialog1.RestoreDirectory = true;
if (fileDialog1.ShowDialog() == DialogResult.OK)
{
GetFileAdd.Text = fileDialog1.FileName;
}
}

private void button2_Click(object sender, EventArgs e)
{
string strResult = "";
try
{

string FilePath = GetFileAdd.Text.Trim();
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FilePath + ";Extended Properties=Excel 8.0;";
craboDbConnection = new OleDbConnection(strConn);
craboDbConnection.Open();
string strsql = "SELECT * FROM [Sheet1$] where 票号 is not null";

OleDbCommand myOleDbCommand = new OleDbCommand(strsql, craboDbConnection);
OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
DataTable excel_info = new DataTable();
excel_info.Load(myDataReader);
craboDbConnection.Close();
int a = excel_info.Rows.Count;

SqlConnection conn = new SqlConnection("server=10.00.00.00;database=Test;UID=sa;PWD=sa");

SqlCommand cmd = null;
conn.Open();
DataTable dtExcel = new DataTable();
dtExcel.Columns.Add("商户号");
dtExcel.Columns.Add("原消费交易日期");
dtExcel.Columns.Add("卡号前四位");
dtExcel.Columns.Add("卡号后四位");
dtExcel.Columns.Add("订单号");
dtExcel.Columns.Add("参考号");
dtExcel.Columns.Add("授权号");
dtExcel.Columns.Add("退货金额");

string filePath = @"D:\Date\";
for (int i = 0; i < a; i++)
{
//新路径
//退款
strsql = "select * from onlinerecord where OnlineRecordTicketNo = '" + excel_info.Rows[i]["票号"] + "'and OnlineRecordStatus = '已授权' AND States = '成功'";
DataTable dtinfo = CreatDT(strsql, "b", conn, "");

double at = 0;
string sqlstr="";

//如果库中有数据（没有则跳出，循环找下一张票号。2011-9-19修改）
if (dtinfo.Rows.Count > 0)
{


string sql = "select OnlineRecordTicketNo,RefundAmount,OnlineRecordRefundDate,AuthCode,RefNum,"+
" OnlineRecordCardNumber ,substring( OnlineRecordCardNumber,0,5 ),substring( OnlineRecordCardNumber,13,4)"+
" from onlinerecord where OnlineRecordTicketNo='" + dtinfo.Rows[0]["OnlineRecordTicketNo"] + "'and OnlineRecordStatus = '退票'";
DataTable dtcount = CreatDT(sql, "b", conn, "");
DataRow dr = dtExcel.NewRow();
string[] datearry = excel_info.Rows[i]["出票日期"].ToString().Split('/');
if (datearry[1].Length == 1)
{
datearry[1] = "0" + datearry[1];
}
if (datearry[2].Substring(0, 2).ToString().Trim().Length == 1)
{
datearry[2] = "0" + datearry[2].Substring(0, 1);
}
//string RefundDate = datearry[0] + datearry[1] + datearry[2];
//2018-08-08出票日期
string RefundDate = datearry[0] +"\\"+ datearry[1] +"\\"+ datearry[2];
  string shopid = "308010702000929";//商户号
string carnum = dtinfo.Rows[0]["OnlineRecordCardNumber"].ToString();
string idaffer = carnum.Substring(0, 5).ToString();//卡号前4位
string idlast = carnum.Substring(12, 4).ToString();//卡号后4位
string orderid = "";//订单号
string refNum = dtinfo.Rows[0]["RefNum"].ToString();//参考号
string authcode = dtinfo.Rows[0]["AuthCode"].ToString();//授权号
string jine = Convert.ToString(Convert.ToDouble(excel_info.Rows[i]["实收款"].ToString().Replace("-", "")).ToString("0.00"));
object[] objs = { shopid, RefundDate, idaffer ,idlast,orderid,refNum,authcode,jine};
dr.ItemArray = objs;
dtExcel.Rows.Add(dr);
dtExcel.TableName = "Sheet1";

dtinfo.Dispose();

#endregion
}
else
{
MessageBox.Show(excel_info.Rows[i]["票号"].ToString()+"，该票不符合要求");
}
}
ReAmount.Text = "生成完毕，共有" + a.ToString() + "条，详细信息如下:" + Convert.ToChar(13) + Convert.ToChar(10) + strResult;
WriteExcel(dtExcel, filePath + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");

}
catch (System.Exception ex)
{

}
}

#region excel导出
public static void WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);

NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].DataType.ToString() == "System.Int32")
{
row2.CreateCell(j).SetCellValue(Convert.ToInt32(dt.Rows[i][j]));
}
else
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}

}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}

#endregion

阅读全文

0 0