注意:服务器需要安装office软件
1、Excel导入
提示:未在本地计算机上注册“Microsoft.Jet.OLEDB.4.0”提供程序。
未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。
解决方法如下:
打开IIS管理器 -> 应用程序管理池 -> 选择相应程序的应用池 ->
右键 -> 应用程序池默认设置 -> 常规选项卡 ->
启用32位应用程序设置成 True
上传excel文件代码
public ActionResult EditAction(FormCollection collection)
{
try
{
string filePath = string.Empty;
string fileType = string.Empty;
HttpPostedFileBase postedFile = Request.Files["fileImage"];
#region 上传文件
fileType = Path.GetExtension(postedFile.FileName).ToLower();//文件的后缀名(小写)
FileUploadResult result = FileHelper.CreatePostedFileToImage(postedFile, @"upload\excel\");
if (result != null && result.Code == (int)CodeEnum.Success)
{
filePath = result.Path;
}
filePath = ConstantParamInfo.PhysicalApplicationPath + filePath;
ImportSql(filePath, fileType);
#endregion
}
catch (Exception ex)
{
}
return RedirectToAction("Index", "CityManage");
}
读取excel文件数据,插入数据库数据代码
public bool ImportSql(string excelPath, string fileType)
{
string strCon = string.Empty;
if (fileType == ".xls")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
else
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "SELECT * FROM [Sheet1$]";
string strComNew = "SELECT * FROM [Sheet2$]";
DataTable dt;
DataTable dtNew;
try
{
#region city table
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, strCon);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[Sheet1$]");
dt = ds.Tables[0];
if (dt != null)
{
if (cityBiz.DeleteAll())
{
for (int i = 0; i < dt.Rows.Count; i++)
{
var model = new City
{
ID = Convert.ToInt32(dt.Rows[i][0].ToString()),
ProvincialID = 0,
Title = dt.Rows[i][1].ToString(),
State = "A"
};
cityBiz.Add(model);
}
}
}
#endregion
#region Hospital table
System.Data.OleDb.OleDbDataAdapter myCommandNew = new System.Data.OleDb.OleDbDataAdapter(strComNew, strCon);
DataSet dsNew = new DataSet();
myCommandNew.Fill(dsNew, "[Sheet2$]");
dtNew = dsNew.Tables[0];
if (dtNew != null)
{
if (hospitalBiz.DeleteAll())
{
for (int i = 0; i < dtNew.Rows.Count; i++)
{
var model = new Hospital
{
CityId = Convert.ToInt32(dtNew.Rows[i][0].ToString()),
Title = dtNew.Rows[i][1].ToString(),
CreateBy = 0
};
hospitalBiz.Add(model);
}
}
}
#endregion
return true;
}
catch (Exception ex)
{
logger.Error("ImportSql()" + ex.Message);
}
return false;
}
2、ASP.NET MVC Excel 下载 数据导出
public ActionResult ExportCSV()
{
try
{
string Transactions = Request.Form["item"];
List<long> arr = new List<long>();
string[] strtemp = Transactions.Split(',');
foreach (string strs in strtemp)
{
arr.Add(long.Parse(strs));
}
//List<PaymentMaster> PaymentList = PaymentBusi.GetTransactionDetails(this.CurrentAccount.SiteID, arr.ToArray());
List<PaymentMaster> PaymentList = new List<PaymentMaster>();
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.WriteLine("AccountName,Date,TransactionID,ItemDesc,Amount,TransactionTotal");
writer.Flush();
for (int i = 0; i < PaymentList.Count; i++)
{
string Summary = string.Empty;
string PaymentIDF = string.Empty;
string PaymentID = string.Empty;
if (PaymentList[i].PaymentItems.Count > 0)
Summary = DelQuota(PaymentList[i].PaymentItems[0].Summary);
if (PaymentList[i].PaymentID > 0)
PaymentIDF = PaymentList[i].PaymentID.ToString();
writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",
DelQuota(PaymentList[i].AccountName),
Convert.ToDateTime(PaymentList[i].CreateDate, GetMDDYYFormat()).ToShortDateString(),
PaymentList[i].PaymentID,
Summary,
"$" + PaymentList[i].PaymentItems[0].Amount.ToString(),
""
));
if (PaymentList[i].PaymentItems.Count > 0)
{
for (int j = 0; j < PaymentList[i].PaymentItems.Count; j++)
{
if (PaymentList[i].PaymentItems[j].PaymentID > 0)
PaymentID = PaymentList[i].PaymentItems[j].PaymentID.ToString();
if (j != 0)
{
writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",
"",
"",
PaymentID,
DelQuota(PaymentList[i].PaymentItems[j].Summary),
"$" + PaymentList[i].PaymentItems[j].Amount.ToString(),
""
));
}
}
writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",
"",
"",
"",
"",
"",
"$" + PaymentList[i].Amount.ToString()
));
}
writer.Flush();
}
stream.Position = 0;
byte[] buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
stream.Close();
return File(buffer, "text/plain", "PaymentInvoice.csv");
}
catch (Exception)
{
return View();
}
}
public string DelQuota(string str)
{
string result = str;
string[] strQuota = { "~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "`", ";", "'", ",", ".", "/", ":", "/,", "<", ">", "?" };
for (int i = 0; i < strQuota.Length; i++)
{
if (result.IndexOf(strQuota[i]) > -1)
result = result.Replace(strQuota[i], "");
}
return result;
}
public System.Globalization.DateTimeFormatInfo GetMDDYYFormat()
{
System.Globalization.DateTimeFormatInfo dtFormat = new System.Globalization.DateTimeFormatInfo();
dtFormat.ShortDatePattern = "M/dd/yy";
return dtFormat;
}
public class PaymentMaster
{
public string PaymentMethod { get; set; }
public long PaymentID { get; set; }
public long AccountID { get; set; }
public long ContactID { get; set; }
public int Status { get; set; }
public long SiteID { get; set; }
public long ClientID { get; set; }
public string LoginName { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Amount { get; set; }
public int DetailCount { get; set; }
public string Description { get; set; }
public System.DateTime ModifyDate { get; set; }
public string CreateDate { get; set; }
public List<long> RentalIDs { get; set; }
public List<PaymentDetail> PaymentItems { get; set; }
public List<string> UnitNumbers { get; set; }
public string AccountName { get; set; }
}
public class PaymentDetail
{
public long DetailID { get; set; }
public int DetailType { get; set; }
public long PaymentID { get; set; }
public long AccountID { get; set; }
public long ContactID { get; set; }
public long RentalID { get; set; }
public int Status { get; set; }
public long SiteID { get; set; }
public string Summary { get; set; }
public string UnitNumber { get; set; }
public string UnitSize { get; set; }
public int UnitCount { get; set; }
public string PaidThruDate { get; set; }
//Public Property Dimensions As String
public string Amount { get; set; }
public string TransactionNumber { get; set; }
public string Description { get; set; }
public System.DateTime ModifyDate { get; set; }
public System.DateTime CreateDate { get; set; }
}
3、ASP.NET Web API Excel 下载 数据导出
[Route("api/area/export")]
[HttpGet]
public HttpResponseMessage Export()
{
List<sys_area> list = new List<sys_area>();
list.Add(new sys_area { code_id = "110000", name = "北京市" });
list.Add(new sys_area { code_id = "130000", name = "河北省" });
#region excel
var runNum = 0;
var workbook = new XSSFWorkbook();
var worksheet = workbook.CreateSheet("Sheet1");
var workRowHead = worksheet.CreateRow(runNum);
workRowHead.CreateCell(0).SetCellValue("编号");
workRowHead.CreateCell(1).SetCellValue("省");
runNum++;
foreach (var item in list)
{
var workRow = worksheet.CreateRow(runNum);
workRow.CreateCell(0).SetCellValue(item.code_id);
workRow.CreateCell(1).SetCellValue(item.name);
runNum++;
}
using (MemoryStream memoryStream = new MemoryStream())
{
workbook.Write(memoryStream);
var result = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
result.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment")
{
FileName = $"地区{DateTime.Now.ToString("yyyyMMddhhmmss")}.xlsx"
};
return result;
}
#endregion
}
Html 前端调用
$("#btnExport").click(function () {
window.location.href = "http://" + window.location.host +"/Area/Export?code=&name=";
});
*、NPOIHelper.cs
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
using System.Text;
namespace Yuebon.Commons.Helpers
{
/// <summary>
/// office 导入导出
/// </summary>
public class NPOIHelper
{
/// <summary>
/// DataTable 导出到 Excel 的 MemoryStream
/// </summary>
/// <param name="dtSource">源 DataTable</param>
/// <param name="strHeaderText">表头文本 空值未不要表头标题</param>
/// <returns></returns>
public static MemoryStream ExportExcel(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "yuebon.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "yuebon.com";
si.ApplicationName = "yuebon.com";
si.LastAuthor = "yuebon.com";
si.Comments = "";
si.Title = "";
si.Subject = "";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int[] arrColWidth=new int[dtSource.Columns.Count];
foreach(DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count;i++ )
{
for (int j = 0; j < dtSource.Columns.Count;j++ )
{
int intTemp = Encoding.GetEncoding("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int intTop = 0;
foreach(DataRow row in dtSource.Rows)
{
#region 新建表、填充表头、填充列头,样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
intTop = 0;
#region 表头及样式
{
if (strHeaderText.Length > 0)
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.Boldweight = 700;
headStyle.SetFont(font);
foreach(DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = intTop;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach(DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV=0;
int.TryParse(drValue,out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV=0;
double.TryParse(drValue,out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using(MemoryStream ms=new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position=0;
return ms;
}
}
/// <summary>
/// DaataTable 导出到 Excel 文件
/// </summary>
/// <param name="dtSource">源 DataaTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置(文件名及路径)</param>
public static void ExportExcel(DataTable dtSource, string strHeaderText,string strFileName)
{
using (MemoryStream ms = ExportExcel(dtSource, strHeaderText))
{
using(FileStream fs=new FileStream(strFileName,FileMode.Create,FileAccess.Write))
{
byte[] data=ms.ToArray();
fs.Write(data,0,data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 读取 excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel 文档路径</param>
/// <returns></returns>
public static DataTable ImportExcel(string strFileName)
{
int ii = strFileName.LastIndexOf(".");
string filetype = strFileName.Substring(ii + 1, strFileName.Length - ii - 1);
DataTable dt = new DataTable();
ISheet sheet;
if ("xlsx" == filetype)
{
XSSFWorkbook xssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
xssfworkbook = new XSSFWorkbook(file);
}
sheet = xssfworkbook.GetSheetAt(0);
}
else
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
sheet = hssfworkbook.GetSheetAt(0);
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row.GetCell(row.FirstCellNum) != null && row.GetCell(row.FirstCellNum).ToString().Length > 0)
{
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
return dt;
}
/// <summary>
/// DataSet 导出到 Excel 的 MemoryStream
/// </summary>
/// <param name="dsSource">源 DataSet</param>
/// <param name="strHeaderText">表头文本 空值未不要表头标题(多个表对应多个表头以英文逗号(,)分开,个数应与表相同)</param>
/// <returns></returns>
public static MemoryStream ExportExcel(DataSet dsSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "yuebon.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "yuebon.com";
si.ApplicationName = "yuebon.com";
si.LastAuthor = "yuebon.com";
si.Comments = "";
si.Title = "";
si.Subject = "";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
#region 注释
//ICellStyle dateStyle = workbook.CreateCellStyle();
//IDataFormat format = workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//ISheet sheet = workbook.CreateSheet();
//int[] arrColWidth = new int[dtSource.Columns.Count];
//foreach (DataColumn item in dtSource.Columns)
//{
// arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").GetBytes(item.ColumnName.ToString()).Length;
//}
//for (int i = 0; i < dtSource.Rows.Count; i++)
//{
// for (int j = 0; j < dtSource.Columns.Count; j++)
// {
// int intTemp = Encoding.GetEncoding("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length;
// if (intTemp > arrColWidth[j])
// {
// arrColWidth[j] = intTemp;
// }
// }
//}
//int rowIndex = 0;
//int intTop = 0;
//foreach (DataRow row in dtSource.Rows)
//{
// #region 新建表、填充表头、填充列头,样式
// if (rowIndex == 65535 || rowIndex == 0)
// {
// if (rowIndex != 0)
// {
// sheet = workbook.CreateSheet();
// }
// intTop = 0;
// #region 表头及样式
// {
// if (strHeaderText.Length > 0)
// {
// IRow headerRow = sheet.CreateRow(intTop);
// intTop += 1;
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
// }
// }
// #endregion
// #region 列头及样式
// {
// IRow headerRow = sheet.CreateRow(intTop);
// intTop += 1;
// ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.Boldweight = 700;
// headStyle.SetFont(font);
// foreach (DataColumn column in dtSource.Columns)
// {
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
// //设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
// }
// }
// #endregion
// rowIndex = intTop;
// }
// #endregion
// #region 填充内容
// IRow dataRow = sheet.CreateRow(rowIndex);
// foreach (DataColumn column in dtSource.Columns)
// {
// ICell newCell = dataRow.CreateCell(column.Ordinal);
// string drValue = row[column].ToString();
// switch (column.DataType.ToString())
// {
// case "System.String"://字符串类型
// newCell.SetCellValue(drValue);
// break;
// case "System.DateTime"://日期类型
// DateTime dateV;
// DateTime.TryParse(drValue, out dateV);
// newCell.SetCellValue(dateV);
// newCell.CellStyle = dateStyle;//格式化显示
// break;
// case "System.Boolean"://布尔型
// bool boolV = false;
// bool.TryParse(drValue, out boolV);
// newCell.SetCellValue(boolV);
// break;
// case "System.Int16":
// case "System.Int32":
// case "System.Int64":
// case "System.Byte":
// int intV = 0;
// int.TryParse(drValue, out intV);
// newCell.SetCellValue(intV);
// break;
// case "System.Decimal":
// case "System.Double":
// double doubV = 0;
// double.TryParse(drValue, out doubV);
// newCell.SetCellValue(doubV);
// break;
// case "System.DBNull"://空值处理
// newCell.SetCellValue("");
// break;
// default:
// newCell.SetCellValue("");
// break;
// }
// }
// #endregion
// rowIndex++;
//}
#endregion
string[] strNewText = strHeaderText.Split(Convert.ToChar(","));
if (dsSource.Tables.Count == strNewText.Length)
{
for(int i=0;i<dsSource.Tables.Count;i++)
{
ExportFromDSExcel(workbook, dsSource.Tables[i], strNewText[i]);
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
/// <summary>
/// DataTable 导出到 Excel 的 MemoryStream
/// </summary>
/// <param name="workbook">源 workbook</param>
/// <param name="dtSource">源 DataTable</param>
/// <param name="strHeaderText">表头文本 空值未不要表头标题(多个表对应多个表头以英文逗号(,)分开,个数应与表相同)</param>
/// <returns></returns>
public static void ExportFromDSExcel(HSSFWorkbook workbook, DataTable dtSource, string strHeaderText)
{
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
ISheet sheet = workbook.CreateSheet(strHeaderText);
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int intTop = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表、填充表头、填充列头,样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
intTop = 0;
#region 表头及样式
{
if (strHeaderText.Length > 0)
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); // 设置设置列宽 太长会报错 修改2014 年9月22日
int dd=(arrColWidth[column.Ordinal] + 1) * 256;
if (dd > 200 * 256)
{
dd = 100 * 256;
}
sheet.SetColumnWidth(column.Ordinal, dd);
}
}
#endregion
rowIndex = intTop;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
if (drValue.Length > 0)
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
}
else { newCell.SetCellValue(drValue); }
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
}
/// <summary>
/// 按指定长度创建列并带入样式
/// </summary>
/// <param name="hssfrow"></param>
/// <param name="len"></param>
/// <param name="cellstyle"></param>
/// <returns></returns>
public static bool CreateCellsWithLength(XSSFRow hssfrow, int len, XSSFCellStyle cellstyle)
{
try
{
for (int i = 0; i < len; i++)
{
hssfrow.CreateCell(i);
hssfrow.Cells[i].CellStyle = cellstyle;
}
return true;
}
catch (Exception ce)
{
throw new Exception("CreateCellsWithLength:" + ce.Message);
}
}
}
}
*
*
*