C# 读取MS数据库并写入到Excel(调用NOPI方式实现)
注:NPOI可以在没有安装Office及WPS的情况下对Word或Excel文档进行读写操作
NOPI组件需自行下载
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
DataTable dt = QueyBasics(参数,参数);
string FileName = @"c:\T1234.xlsx";
DataTableTOExcel(FileName,dt,"MySheet",true);
public DataTable QueyBasics(string ConStr ,string Sql)
{ //查询数据库方法,返回DataTable对象 参数:ConStr数据库连接串 Sql查询语句
//ConStr = "server=[地址]192.168..;database=[数据库名称]AIS2020...;uid=[用户名]sa;pwd=密码ABC.."
string strConn = ConStr;
string strSql = Sql;
SqlDataAdapter da = new SqlDataAdapter(strSql, strConn);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public bool DataTableToExcel(string FileName,DataTable sourceData,string sheetName,bool IsWriteColumnName)
{ if (!File.Exists(FileName))
{
FileStream fs = File.Create(FileName);
fs.Close();
//Console.WriteLine("创建文件完毕");
}
//根据Excel文件的后缀名创建对应的workbook
IWorkbook workbook = null;
if(FileName.IndexOf(".xlsx")> 0){ //2007版本的excel
workbook = new XSSFWorkbook();
}
else if (FileName.IndexOf(".xls") > 0) //2003版本的excel
{
workbook = new HSSFWorkbook();
}
else {
return false; //都不匹配或者传入的文件根本就不是excel文件,直接返回
}
//excel表的sheet名
ISheet sheet = workbook.CreateSheet(sheetName);
if (sheet == null) return false; //无法创建sheet,则直接返回
//写入Excel的行数
int WriteRowCount = 0;
//指明需要写入列名,则写入DataTable的列名,第一行写入列名
if(IsWriteColumnName){
//sheet表创建新的一行,即第一行
IRow ColumnNameRow=sheet.CreateRow(0); //0下标代表第一行
//进行写入DataTable的列名
for (int colunmNameIndex = 0; colunmNameIndex < sourceData.Columns.Count;colunmNameIndex++ )
{
ColumnNameRow.CreateCell(colunmNameIndex).SetCellValue(sourceData.Columns[colunmNameIndex].ColumnName);
}
WriteRowCount++;
}
//写入数据
for (int row=0; row < sourceData.Rows.Count;row++)
{
//sheet表创建新的一行
IRow newRow = sheet.CreateRow(WriteRowCount);
for (int column=0; column < sourceData.Columns.Count;column++)
{
newRow.CreateCell(column).SetCellValue(sourceData.Rows[row][column].ToString());
}
WriteRowCount++; //写入下一行
}
for (int columnNum = 0; columnNum <= 10; columnNum++)
{ //调整列宽
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow = sheet.GetRow(rowNum);
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//写入到excel中
FileStream fs = new FileStream(FileName,FileMode.Open,FileAccess.Write);
workbook.Write(fs);
//fs.Flush();
fs.Close();
return true;
}