/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel1(DataTable dtSource, string strHeaderText)
{
Helper helper = new Helper("Data Source=172.128.2.1/veims;User ID=zte;Password=zsfyqch;");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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(936).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(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
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);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
string dtname = row[1].ToString();
#region
string sql = string.Format(@"select cc.COMMENTS, cC.COLUMN_NAME,
CASE WHEN c.data_type='VARCHAR2' THEN '字符串型C'
WHEN c.data_type='NUMBER' THEN '数值型N'
WHEN c.data_type='CHAR' THEN '字符串型C'
WHEN c.data_type='DATE' THEN '日期时间型T'
WHEN c.data_type='FLOAT' THEN '数值型N'
WHEN c.data_type='CLOB' THEN '长文本text'
WHEN c.data_type='NCHAR' THEN '字符串型C'
WHEN c.data_type='RAW' THEN '字符串型C'
WHEN c.data_type='NVARCHAR2' THEN '字符串型C' END data_type
,
c.data_length
,'无条件共享' A1,
'用户数据校核、业务协同及大数据应用' A2,'共享平台方式'A3,'数据库'A4,'否'A5
from user_tab_columns c
LEFT JOIN
user_col_comments cc
ON cc.COLUMN_NAME=c.COLUMN_NAME AND CC.Table_Name='{0}'
where
c.Table_Name='{0}' order by c.COLUMN_NAME asc", dtname.ToUpper());
#endregion
DataTable dd = helper.ExecuteDataSet(sql).Tables[0];
int kk = 0;//列控制 0到16列、27、28列 全部合并单元格
foreach (DataColumn column in dtSource.Columns)
{
//第一列合并第三行到第五行 0 1 2 3 4
//sheet.AddMergedRegion(new CellRangeAddress(2, 4, 0, 0));
if (dd.Rows.Count > 0 && kk < 17)
{
// 0到16列全部合并单元格
// 第KK列合并第2行到第dd.Rows.Count行
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + dd.Rows.Count, kk, kk));
}
if (kk > 26 && kk < 29)
{
//0到16列全部合并单元格
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + dd.Rows.Count, kk, kk));
}
HSSFCell newCell;
string drValue;
//if ((kk > 16 && kk < 27) || (kk > 26 && kk < 29))
//{
// int f = 0;
// foreach (DataRow row1 in dd.Rows)
// {
// rowIndex++;
// foreach (DataColumn column1 in dd.Columns)
// {
// newCell = (HSSFCell)dataRow.CreateCell(kk+column1.Ordinal);
// drValue = row1[column1].ToString();
// switch (column1.DataType.ToString())
// {
// case "System.String"://字符串类型
// newCell.SetCellValue(drValue);
// break;
// case "System.DateTime"://日期类型
// System.DateTime dateV;
// System.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;
// }
// }
// f++;
// }
//}
//else
{
newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
}
}
kk++;
}
#endregion
rowIndex += dd.Rows.Count;
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
DateTime minday, maxday = DateTime.Now;
HSSFWorkbook hssfworkbook;
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int i = 0, count = getRowsCount() - 1;
bool flag = true;
try
{
IRow headerRow = sheet.GetRow(0);
if (headerRow.GetCell(0).ToString() != "零售商ID" ||
headerRow.GetCell(1).ToString() != "零售商编号" ||
headerRow.GetCell(2).ToString() != "零售商名称" ||
headerRow.GetCell(3).ToString() != "零售商分类" ||
headerRow.GetCell(4).ToString() != "归属月份" ||
headerRow.GetCell(5).ToString() != "导购ID" ||
headerRow.GetCell(6).ToString() != "导购姓名")
{
flag = false;
}
if (!flag)
{
MessageBox.Show(this, "表头(1~7列)错误!");
return;
}
int column = getColumnCount(headerRow) + 1;
int month = 0;
rows.MoveNext();
while (rows.MoveNext())
{
int datacolumn = 7;
i++;
HSSFRow row = (HSSFRow)rows.Current;
if (row.GetCell(0).ToString() == "")
{
break;
}
int clientid = 0; int promotorid = 0;
if (!int.TryParse(row.GetCell(0).ToString(), out clientid))
{
errormessage += "零售商：" + row.GetCell(2).ToString() + "的ID错误;\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null)
{
errormessage += "ID号：" + clientid.ToString() + "零售商在系统中不存在！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if (int.TryParse(row.GetCell(5).ToString(), out promotorid))
{
PM_Promotor pm = new PM_PromotorBLL(promotorid).Model;
if (pm == null)
{
errormessage += "导购ID号：" + promotorid.ToString() + "导购在系统中不存在！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
}
if (month == 0 && headerRow.GetCell(4).ToString() == "归属月份")
{
IList <AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + row.GetCell(4).ToString() + "'");
if (_monthlist.Count > 0)
{
month = _monthlist[0].ID;
minday = _monthlist[0].BeginDate;
maxday = DateTime.Today < _monthlist[0].EndDate ? DateTime.Today : _monthlist[0].EndDate;
}
else
{
errormessage += "会计月错误;\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
}
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
string conditon = "";
conditon = "Supplier=" + clientid.ToString()
+ "AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',4)='7' AND Type=3 AND AccountMonth=" + month.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND Flag=1 AND ISNULL(Promotor,0)=" + promotorid.ToString();
IList <SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList(conditon);
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
errormessage += "ID号：" + row.GetCell(0).ToString() + "," + client.FullName
+ "，导购:" + row.GetCell(6).ToString() + " 当月的销量单" + "已审核，不可再次导入！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if (svmlists.Count == 1)
{
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
}
if (bll == null)
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = 0;
bll.Model.Supplier = client.ID;
bll.Model.Promotor = promotorid;
bll.Model.Type = 3;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.AccountMonth = month;
bll.Model.SalesDate = maxday;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = 1;
bll.Model.Remark = "线下补录导入";
}
#endregion
bll.Model["SubmitFlag"] = "1";
bll.Model["DataSource"] = "7";
IList <SVM_SalesVolume_Detail> details = new List <SVM_SalesVolume_Detail>();
bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
int quantity = 0;
bool isnumber = false;
while (true)
{
PDT_Product product = null;
quantity = 0;
if (headerRow.GetCell(datacolumn) == null || headerRow.GetCell(datacolumn).CellType == CellType.BLANK || headerRow.GetCell(datacolumn).ToString() == string.Empty)
{
break;
}
IList <PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + headerRow.GetCell(datacolumn).ToString() + "' AND State=1");
if (products.Count > 0)
{
product = products[0];
}
else
{
errormessage += "产品名称：" + headerRow.GetCell(datacolumn).ToString() + "在产品列表中不存在！\r\n";
datacolumn++;
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if ((product != null) && row.GetCell(datacolumn).CellType != CellType.BLANK)
{
int.TryParse(row.GetCell(datacolumn).ToString(), out quantity);
if (int.TryParse(row.GetCell(datacolumn).ToString(), out quantity) && !isnumber)
{
isnumber = true;
}
if (quantity != 0 && quantity <= 5000 && quantity >= 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 3, out factoryprice, out salesprice);
if (factoryprice == 0)
{
factoryprice = product.FactoryPrice;
}
if (salesprice == 0)
{
salesprice = product.NetPrice;
}
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
else if (row.GetCell(datacolumn).CellType != CellType.BLANK && (row.GetCell(datacolumn).ToString() != "0"))
{
wrongflag = true;
break;
}
else if (quantity < 0)
{
wrongflag = true;
break;
}
}
datacolumn++;
}
if (wrongflag)
{
errormessage += "ID号：" + clientid.ToString() + "，" + client.FullName
+ "，导购:" + row.GetCell(6).ToString() + " 当月的线下补录销量单"
+ "未能导入！产品名称：" + headerRow.GetCell(datacolumn).ToString() + "数量填写错误。\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.Model.UpdateStaff = 1;
bll.AddDetail();
bll.Update();
string message = " ID号：" + clientid.ToString() + "，该零售商：" + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + "的销量单" + "被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "\r\n";
improtmessage += message;
row.CreateCell(column).SetCellValue(message);
}
if (details.Count == 0 && isnumber)
{
bll.DeleteDetail();
}
}
else
{
if (details.Count > 0 || svmlists.Count == 0) //没有产品也新增一条空销量头
{
bll.Items = details;
if (bll.Add() > 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
}
string message = "ID号：" + clientid.ToString() + "，该零售商：" + client.FullName
+ "的销量单" + "已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "\r\n";
improtmessage += message;
row.CreateCell(column).SetCellValue(message);
}
}
#endregion
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
FileStream writefile = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
hssfworkbook.Write(writefile);
writefile.Close();
sheet = null;
}
}
/// <summary>填充内容</summary>
static void FillContent(HSSFRow hssfRow
, DataColumn column, DataRow row
, HSSFCellStyle contentStyle, HSSFFont contentFont
, HSSFCellStyle contentDateStyle
, HSSFCellStyle contentStyleDailyBalance, HSSFFont fontDailyBalance
, THOK.NPOI.Models.ExportParam ep
, HSSFSheet sheet, string headTextStrA)
{
HSSFCell newCell = hssfRow.CreateCell(column.Ordinal) as HSSFCell;
#region 当数据访问转换DataTime时生效
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
contentDateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#endregion
string columnF = row[column].ToString();
decimal i;
bool b = decimal.TryParse(columnF, out i);
#region 判断如果是仓库库存日结核对
if (ep.ContentModule == "DailyBalance" && sheet == workbook.GetSheet(headTextStrA) &&
((column.Ordinal == 5 && b == false) ||
(column.Ordinal == 6 && b == false) ||
(column.Ordinal == 7 && b == false) ||
(column.Ordinal == 8 && b == false) ||
(column.Ordinal == 9 && b == false) ||
(column.Ordinal == 10 && b == false)))
{
fontDailyBalance.FontName = ep.ColHeadFont;
fontDailyBalance.FontHeightInPoints = ep.ColHeadSize;
fontDailyBalance.Color = ep.ContentModuleColor;
contentStyleDailyBalance.SetFont(fontDailyBalance);
if (ep.ColHeadBorder == true)
{
contentStyleDailyBalance.BorderBottom = BorderStyle.THIN;
contentStyleDailyBalance.BorderLeft = BorderStyle.THIN;
contentStyleDailyBalance.BorderRight = BorderStyle.THIN;
contentStyleDailyBalance.BorderTop = BorderStyle.THIN;
}
hssfRow.GetCell(column.Ordinal).CellStyle = contentStyleDailyBalance;
}
#endregion
else
{
contentFont.FontName = ep.ColHeadFont;
contentFont.FontHeightInPoints = ep.ColHeadSize;
contentFont.Color = ep.ContentColor;
contentStyle.SetFont(contentFont);
//画边框
if (ep.ColHeadBorder == true)
{
contentStyle.BorderBottom = BorderStyle.THIN;
contentStyle.BorderLeft = BorderStyle.THIN;
contentStyle.BorderRight = BorderStyle.THIN;
contentStyle.BorderTop = BorderStyle.THIN;
}
hssfRow.GetCell(column.Ordinal).CellStyle = contentStyle;
}
string drValue = row[column].ToString();
THOK.NPOI.Common.ExportExcelHeper.ChangeFormat(column, drValue, newCell, contentDateStyle);
}
private void btnImportData_Click(object sender, EventArgs e)
{
using (FileStream fsRead = File.OpenRead(@txtFilePathName.Text))
{
HSSFWorkbook workbook = new HSSFWorkbook(fsRead);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
string insert_sql = "insert into product_deliveryinfo values(@contractNumber,@customerName,@deliveryDate,@carNumber,@pType,@pStandard, @pRollNumber, @pName, @pGrade, @pLength,@pEachBundle,@pNumber,@pWeight)";
for (int r = 1; r <= sheet.LastRowNum; r++)
{
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@contractNumber", SqlDbType.NVarChar, 50),
new SqlParameter("@customerName", SqlDbType.NVarChar, 50),
new SqlParameter("@deliveryDate", SqlDbType.DateTime),
new SqlParameter("@carNumber", SqlDbType.NVarChar, 50),
new SqlParameter("@pType", SqlDbType.NVarChar, 50),
new SqlParameter("@pStandard", SqlDbType.NVarChar, 50),
new SqlParameter("@pRollNumber", SqlDbType.NVarChar, 50),
new SqlParameter("@pName", SqlDbType.NVarChar, 50),
new SqlParameter("@pGrade", SqlDbType.NVarChar, 50),
new SqlParameter("@pLength", SqlDbType.Int),
new SqlParameter("@pEachBundle", SqlDbType.NVarChar, 50),
new SqlParameter("@pNumber", SqlDbType.Int),
new SqlParameter("@pWeight", SqlDbType.Float)
};
HSSFRow currentRow = (HSSFRow)sheet.GetRow(r);
if (currentRow != null)
{
for (int c = 1; c < currentRow.LastCellNum; c++)
{
HSSFCell cell = (HSSFCell)currentRow.GetCell(c);
if (cell == null || cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
//表示单元格为空，需要向数据库插入DBNull.value
ps[c - 1].Value = DBNull.Value;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
//c=3 日期转化
if (c == 3)
{
ps[c - 1].Value = DateTime.FromOADate(cell.NumericCellValue);
}
else if (c == 7 || c == 11)
{
ps[c - 1].Value = cell.ToString();
}
else
{
ps[c - 1].Value = cell.NumericCellValue;
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
ps[c - 1].Value = cell.ToString();
}
}
SqlHelper.ExecuteNonQuery(insert_sql, CommandType.Text, ps);
}
}
}
MessageBox.Show("数据导入成功！");
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="FileName">文件名称</param>
/// <param name="exceltype">是否模板</param>
public void DataTableToExcel(DataTable dtSource, string ExportType, string strHeaderText, string FileName = "")
{
NPOI.SS.UserModel.IWorkbook workbook = null;
SaveFileDialog savefd = new SaveFileDialog();
if (FileName != "")
{
savefd.FileName = FileName + ".xls";
}
savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
if (savefd.FilterIndex == 1)
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ExportType);
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = SetExcelColWidth(dtSource, ExportType);
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet(ExportType);
}
#region 表头及样式
{
if (strHeaderText != "")
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 18;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
rowIndex = 1;
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 11;
font.Boldweight = 700;
font.IsBold = true;
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);
}
// headerRow.Dispose();
}
#endregion
rowIndex++;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
sheet = null;
using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 从 excel 读取数据 为datatable
/// </summary>
/// <returns></returns>
public static DataTable GetExcelData(Stream s, int startRow)
{
DataTable dt = new DataTable();
HSSFWorkbook workbook = new HSSFWorkbook(s);
HSSFSheet sheet = workbook.GetSheetAt(0) as HSSFSheet;
int firstRowIndex = startRow - 1;
//获取sheet的首行
HSSFRow headerRow = sheet.GetRow(firstRowIndex) as HSSFRow;
while (headerRow == null)
{
firstRowIndex += 1;
headerRow = sheet.GetRow(firstRowIndex) as HSSFRow;
}
//获取列数
int cellCount = headerRow.PhysicalNumberOfCells;
//获取行数
int rowCount = sheet.PhysicalNumberOfRows;
for (int i = firstRowIndex; i < startRow; i++)
{
var row = sheet.GetRow(i) as HSSFRow;
if (row != null)
{
rowCount++;
}
}
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
string colName = headerRow.GetCell(i) != null?headerRow.GetCell(i).StringCellValue : "col" + i.ToString();
DataColumn column = new DataColumn(colName);
dt.Columns.Add(column);
}
///从标题行+1开始循环
for (int i = startRow; i < rowCount; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
if (row == null)
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dr[j] = null;
}
else
{
//读取Excel格式，根据格式读取数据类型
switch (cell.CellType)
{
case CellType.BLANK: //空数据类型处理
dr[j] = "";
break;
case CellType.STRING: //字符串类型
dr[j] = cell.StringCellValue.Trim();
break;
case CellType.NUMERIC: //数字类型
//判断格式是否为日期
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.FORMULA:
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
dr[j] = e.Evaluate(cell).StringValue;
break;
default:
dr[j] = "";
break;
}
}
}
dt.Rows.Add(dr);
}
workbook = null;
sheet = null;
return(dt);
}
/// <summary>读取excel
/// 根据索引读取Sheet表数据，默认读取第一个sheet
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetIndex">sheet表的索引，从0开始</param>
/// <returns>数据集</returns>
public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
xssfworkbook = new XSSFWorkbook(file);//初始化太慢了，不知道这是什么bug
}
}
if (hssfworkbook != null)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
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);
}
}
}
else if (xssfworkbook != null)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
if (xSheet != null)
{
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
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);
}
public void Export2Excel(string fileName, DataSet data)
{
MemoryStream ms = new MemoryStream();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "xi";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "xi";
si.ApplicationName = "xi";
si.LastAuthor = "xi";
si.CreateDateTime = DateTime.Now;
hssfworkbook.SummaryInformation = si;
#endregion
#region Excel单元格格式
int rowIndex = 0;
HSSFRow headRow = null;
HSSFRow titleRow = null;
HSSFRow dataRow = null;
HSSFCell cell = null;
HSSFCellStyle headStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
HSSFFont headfont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(headfont);
HSSFCellStyle titleStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont titlefont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(titlefont);
HSSFCellStyle cellDateStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellDateFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
if ("Y" == "Y")
{
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd HH:mm");
}
else
{
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd");
}
//数量小数格式化字符串
HSSFCellStyle cellNumStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellNumFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
string formatValue = "0";
string formatStr = string.Empty;
switch (formatValue)
{
#region 格式化
case "0":
formatStr = "0";
break;
case "1":
formatStr = "0.0";
break;
case "2":
formatStr = "0.00";
break;
case "3":
formatStr = "0.000";
break;
case "4":
formatStr = "0.0000";
break;
case "5":
formatStr = "0.00000";
break;
default:
formatStr = "{0:0}";
break;
#endregion
}
if (formatStr == "0" || formatStr == "0.00")
{
cellNumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(formatStr);
}
else
{
cellNumStyle.DataFormat = cellNumFormat.GetFormat(formatStr);
}
//日期格式字符串
string dateFormat = "yyyy-MM-dd";
if ("Y" == "Y")
{
dateFormat += " " + "HH:mm";
}
#endregion
foreach (DataRow row in data.Tables[0].Rows)
{
#region 新建sheet 填写表头 列头
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)hssfworkbook.CreateSheet();
}
#region 列头
titleRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
titleRow.CreateCell(i).SetCellValue(data.Tables[0].Columns[i].ColumnName);
titleRow.GetCell(i).CellStyle = titleStyle;
}
rowIndex++;
#endregion
}
#endregion
#region 明细
dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
cell = (HSSFCell)dataRow.CreateCell(i);
string value = row[data.Tables[0].Columns[i].ColumnName].ToString();
switch (data.Tables[0].Columns[i].DataType.FullName)
{
case "System.String":
cell.SetCellValue(value);
break;
case "System.DateTime":
DateTime datevalue;
DateTime.TryParse(value, out datevalue);
if (datevalue != new DateTime())
{
cell.CellStyle = cellDateStyle;
cell.SetCellValue(DateTime.Parse(datevalue.ToString(dateFormat)));
}
else
{
cell.SetCellValue("");
}
break;
case "System.Boolean":
bool boolvalue = false;
bool.TryParse(value, out boolvalue);
cell.SetCellValue(boolvalue);
break;
case "System.Int16":
Int16 int16 = 0;
Int16.TryParse(value, out int16);
cell.SetCellValue(int16);
break;
case "System.Int32":
Int32 int32 = 0;
Int32.TryParse(value, out int32);
cell.SetCellValue(int32);
break;
case "System.Int64":
Int64 int64 = 0;
Int64.TryParse(value, out int64);
cell.SetCellValue(int64);
break;
case "System.Byte":
int intV = 0;
int.TryParse(value, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal":
double decV = 0;
double.TryParse(value, out decV);
cell.SetCellValue(decV);
cell.CellStyle = cellNumStyle;
break;
case "System.Single":
float floatV = 0;
float.TryParse(value, out floatV);
cell.SetCellValue(floatV);
cell.CellStyle = cellNumStyle;
break;
case "System.Double":
double doubV = 0;
double.TryParse(value, out doubV);
cell.SetCellValue(doubV);
cell.CellStyle = cellNumStyle;
break;
case "System.DBNull":
cell.SetCellValue("");
break;
default:
cell.SetCellValue(value);
break;
}
}
#endregion
rowIndex++;
}
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "GB2312";
HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Response.BinaryWrite(ms.ToArray());
HttpContext.Response.End();
ms.Close();
sheet = null;
hssfworkbook = null;
ms = null;
}
public ActionResult index()
{
Dictionary <string, object> dic = new Dictionary <string, object>();
JsonResult ret = new JsonResult {
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
try
{
Response.ContentType = "text/plain";
Response.Charset = "utf-8";
HttpPostedFileBase file = Request.Files["file"];
string filePath = Server.MapPath("\\file") + "\\";
if (file != null)
{
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
}
else
{
dic.Add("success", "false");
dic.Add("message", "请选择需要上传的文件");
ret.Data = dic;
return(ret);
}
string fileExtension = Path.GetExtension(file.FileName);
if (fileExtension.ToUpper() != ".XLS" && fileExtension.ToUpper() != ".XLSX")//&& fileExtension.ToUpper() != ".XLSM" && fileExtension.ToUpper() != ".XLSB")
{
dic.Add("success", "false");
dic.Add("message", "上传的文件类型错误");
ret.Data = dic;
return(ret);
}
var path = filePath + file.FileName;
file.SaveAs(path);
List <KneeCoordinateModal> list = new List <KneeCoordinateModal>();
using (FileStream f = new FileStream(path, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(f);
ISheet sheet = workbook.GetSheetAt(0);
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
HSSFRow r = (HSSFRow)sheet.GetRow(i);
KneeCoordinateModal coordinate = new KneeCoordinateModal
{
CoordinateX = Convert.ToDouble(r.GetCell(0).ToString()),
CoordinateY = Convert.ToDouble(r.GetCell(1).ToString())
};
list.Add(coordinate);
}
}
dic.Add("success", "true");
dic.Add("message", "文件导入成功");
dic.Add("data", list);
System.IO.File.Delete(path);
}
catch (Exception exception)
{
dic = new Dictionary <string, object>();
dic.Add("success", "false");
dic.Add("message", exception.Message);
}
ret.Data = dic;
return(ret);
}
/// <summary>
/// NPOI DataGridView 导出 EXCEL
/// </summary>
/// <param name="fileName"> 默认保存文件名</param>
/// <param name="dgv">DataGridView</param>
/// <param name="fontname">字体名称</param>
/// <param name="fontsize">字体大小</param>
public static void ExportExcel(DataGridView dgv, string fontname, short fontsize)
{
//检测是否有数据
if (dgv.Rows.Count == 0)
{
return;
}
//创建主要对象
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight");
//设置字体，大小，对齐方式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = fontname;
font.FontHeightInPoints = fontsize;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //居中对齐
//添加表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible)
{
dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.GetCell(i).CellStyle = style;
}
}
//注释的这行是设置筛选的
//sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count));
//添加列及内容
for (int i = 0; i < dgv.Rows.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible)
{
string ValueType = dgv.Rows[i].Cells[j].Value.GetType( ).ToString( );
string Value = dgv.Rows[i].Cells[j].Value.ToString( );
switch (ValueType)
{
case "System.String": //字符串类型
dataRow.CreateCell(j).SetCellValue(Value);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(Value, out dateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(Value, out boolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(Value, out intV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(Value, out doubV);
dataRow.CreateCell(j).SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
dataRow.CreateCell(j).SetCellValue("");
break;
default:
dataRow.CreateCell(j).SetCellValue("");
break;
}
dataRow.GetCell(j).CellStyle = style;
//设置宽度
sheet.SetColumnWidth(j, (Value.Length + 10) * 256);
}
else
{
sheet.SetColumnHidden(j, true);
}
}
}
//保存文件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
MemoryStream ms = new MemoryStream();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件被站用，请关闭文件 " + saveFileName);
workbook = null;
ms.Close();
ms.Dispose();
return;
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show(saveDialog.FileName + " 保存成功", "提示", MessageBoxButtons.OK);
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
}
public static void ExportDT(DataTable dtSource, string filename)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.huobanplus.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "杭州火图科技有限公司"; //填加xls文件作者信息
si.ApplicationName = "火淘助手"; //填加xls文件创建程序信息
si.LastAuthor = "hot"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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(936).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(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
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;
//设置列宽
if (arrColWidth[column.Ordinal] > 255)
{
arrColWidth[column.Ordinal] = 254;
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
//rowIndex = 2;
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
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 (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
}
Dictionary <string, ConfigFile> ReadXML(string path)
{
//path = Application.dataPath + "/../Builder/builder_config.xls";
Dictionary <string, ConfigFile> ret = new Dictionary <string, ConfigFile>();
// !< 说明:客户端的表读取,就不走服务器那样的复杂处理啦..
HSSFWorkbook wk = new HSSFWorkbook(File.OpenRead(path));
HSSFSheet sheet = null;
string sheetname = "";
for (int a = 0; a < wk.NumberOfSheets; ++a)
{
sheet = wk.GetSheetAt(a) as HSSFSheet;
sheetname = wk.GetSheetName(a);
if (sheet == null)
{
Debug.LogError("异常!" + wk.GetFullName());
continue;
}
if (ret.ContainsKey(sheetname))
{
Debug.LogError("该excel表的sheet名字重复了：" + wk.GetSheetName(a));
}
else
{
ConfigFile result = new ConfigFile(wk.GetFullName() + "_" + sheetname);
List <String> header = new List <string>();
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
if (row == null)
{
continue;
}
ArrayList line = new ArrayList();
for (int j = row.FirstCellNum; j <= row.LastCellNum; ++j)
{
HSSFCell cell = row.GetCell(j) as HSSFCell;
if (cell == null)
{
continue;
}
if (i == sheet.FirstRowNum)
{
header.Add(cell.ToString());
}
else
{
line.Add(cell.ToString());
}
}
if (i == sheet.FirstRowNum)
{
result.SetTitles(header.ToArray());
}
else
{
if (line.Count > 0)
{
result.AddData(line[0] as String, line);
}
else
{
// !< 读完了?
break;
}
}
}
ret.Add(sheetname, result);
}
}
return(ret);
}
/// <summary>
/// 从Excel中读取一个工作薄，生成Datatable对象。
/// </summary>
/// <param name="xlsFile"></param>
/// <param name="sheetIndex"></param>
/// <returns></returns>
private DataTable SheetToDatatable(string xlsFile, int sheetIndex)
{
DataTable dt = new DataTable();
//获取扩展名
string ext = xlsFile.Substring(xlsFile.LastIndexOf(".") + 1);
if (ext.ToLower() == "xls")
{
using (FileStream file = new FileStream(xlsFile, FileMode.Open, FileAccess.Read))
{
//创建工作薄对象
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
try
{
rows.MoveNext();
//创建Datatable结构
HSSFRow firsRow = (HSSFRow)rows.Current;
for (int i = 0; i < firsRow.LastCellNum; i++)
{
ICell cell = firsRow.GetCell(i);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
dt.Columns.Add(new DataColumn(cell.ToString(), getColumnType(cell.ToString())));
}
//导入工作薄的数据
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
continue;
}
string value = cell.ToString();
//读取Excel格式，根据格式读取数据类型
switch (dt.Columns[i].DataType.FullName)
{
case "System.DateTime": //日期类型
if (DateUtil.IsValidExcelDate(cell.NumericCellValue))
{
try
{
value = cell.DateCellValue.ToString();
}
catch
{
value = cell.ToString();
}
}
else
{
value = cell.NumericCellValue.ToString();
}
break;
default:
value = cell.ToString();
break;
}
dr[i] = WeiSha.Common.Param.Method.ConvertToAnyValue.Get(value).ChangeType(dt.Columns[i].DataType);
}
dt.Rows.Add(dr);
}
}
catch
{
return(dt);
}
}
}
if (ext.ToLower() == "xlsx")
{
using (OleDbConnection conn = new OleDbConnection(string.Format(connStr, xlsFile)))
{
conn.Open();
//获取工作簿
DataTable dtSheet = this.GetSheets(xlsFile);
string table = dtSheet.Rows[sheetIndex]["Name"].ToString();
//获取工作薄数据
DataTable dtSheetData = new DataTable();
new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", table + "$"), conn).Fill(dtSheetData);
for (int i = 0; i < dtSheetData.Columns.Count; i++)
{
dt.Columns.Add(dtSheetData.Rows[0][i].ToString(), Type.GetType("System.String"));
}
for (int i = 1; i < dtSheetData.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < dtSheetData.Columns.Count; j++)
{
dr[j] = dtSheetData.Rows[i][j].ToString();
}
dt.Rows.Add(dr);
}
//
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(dt);
}
static void Main(string[] args)
{
string outPutPath = @"E:\日盛文件\Winmatrix最高權限批次匯入申請表單_網頁平台組\AutoCreate\";
string sourceDataPath = @"E:\日盛文件\Winmatrix最高權限批次匯入申請表單_網頁平台組\AutoCreate\data.xls";
var dataList = GetSourceData(sourceDataPath);
DateTime NxtMon = GetNxtWeekMonday();
int rowNum = 1;
for (int i = 1; i <= 5; i++)
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("最高權限");
var fileNm = NxtMon.ToString("yyyy/MM/dd");
//設定樣式
ICellStyle headerStyle = wb.CreateCellStyle();
IFont headerFont = wb.CreateFont();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.FillForegroundColor = HSSFColor.Black.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerFont.FontName = "微軟正黑體";
headerFont.Color = IndexedColors.White.Index;
headerFont.FontHeightInPoints = 12;
headerStyle.SetFont(headerFont);
sheet.CreateRow(0);
HSSFRow titleRow = (HSSFRow)sheet.GetRow(0);
titleRow.CreateCell(0, CellType.String).SetCellValue("申請帳號");
titleRow.CreateCell(1, CellType.String).SetCellValue("電腦名稱");
titleRow.CreateCell(2, CellType.String).SetCellValue("使用人(電子郵件)");
titleRow.CreateCell(3, CellType.String).SetCellValue("申請期間(起始時間)");
titleRow.CreateCell(4, CellType.String).SetCellValue("申請期間(結束時間)");
titleRow.CreateCell(5, CellType.String).SetCellValue("申請原因");
titleRow.CreateCell(6, CellType.String).SetCellValue("備註");
titleRow.GetCell(0).CellStyle = headerStyle;
titleRow.GetCell(1).CellStyle = headerStyle;
titleRow.GetCell(2).CellStyle = headerStyle;
titleRow.GetCell(3).CellStyle = headerStyle;
titleRow.GetCell(4).CellStyle = headerStyle;
titleRow.GetCell(5).CellStyle = headerStyle;
titleRow.GetCell(6).CellStyle = headerStyle;
foreach (var rowData in dataList.Skip(1))
{
sheet.CreateRow(rowNum);
HSSFRow row = (HSSFRow)sheet.GetRow(rowNum++);
row.CreateCell(0, CellType.String).SetCellValue(rowData.Account);
row.CreateCell(1, CellType.String).SetCellValue(rowData.ComputerNM);
row.CreateCell(2, CellType.String).SetCellValue(rowData.Email);
row.CreateCell(3, CellType.String).SetCellValue(fileNm + " 08:00:00");
row.CreateCell(4, CellType.String).SetCellValue(fileNm + " 23:59:00");
row.CreateCell(5, CellType.String).SetCellValue(rowData.Reason);
row.CreateCell(6, CellType.String).SetCellValue(rowData.Remark);
}
FileStream file = new FileStream(outPutPath + NxtMon.ToString("yyyyMMdd") + ".xls", FileMode.Create, FileAccess.Write);
wb.Write(file);
file.Close();
SetColumnWidth(sheet);
rowNum = 1;
NxtMon = NxtMon.AddDays(1);
}
}
/// <summary>
/// 用于Web导出
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
/// <Author>卜永济www.cnblogs.com/BuBu/ 2013-6-7 22:21:41</Author>
//todo暂时屏蔽，建议放在Rdp.Web.Framework中扩展
/*public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
* {
*
* HttpContext curContext = HttpContext.Current;
*
* // 设置编码和附件格式
* curContext.Response.ContentType = "application/vnd.ms-excel";
* curContext.Response.ContentEncoding = Encoding.UTF8;
* curContext.Response.Charset = "";
* curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
*
* curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
* curContext.Response.End();
*
* }*/
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = default(HSSFWorkbook);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j <= cellCount - 1; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j <= cellCount - 1; j++)
{
if (row.GetCell(j) != null)
{
//dataRow(j) = row.GetCell(j).ToString()
HSSFCell cell = (HSSFCell)row.GetCell(j);
if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
switch (cell.CachedFormulaResultType)
{
case NPOI.SS.UserModel.CellType.String:
dataRow[j] = row.GetCell(j).StringCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
dataRow[j] = row.GetCell(j).NumericCellValue;
break;
default:
dataRow[j] = row.GetCell(j).ToString();
break;
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
//while (rows.MoveNext())
//{
// HSSFRow row = (HSSFRow)rows.Current;
// DataRow dr = dt.NewRow();
// for (int i = 0; i < row.LastCellNum; i++)
// {
// HSSFCell cell = row.GetCell(i);
// if (cell == null)
// {
// dr[i] = null;
// }
// else
// {
// dr[i] = cell.ToString();
// }
// }
// dt.Rows.Add(dr);
//}
return(dt);
}
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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(936).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(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
}
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
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);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 将Excel里的数据分页显示
/// </summary>
/// <param name="strFileName">文件名</param>
/// <param name="pageSize">每页显示行数</param>
/// <param name="currentPage">当前页数</param>
/// <param name="total">总行数</param>
/// <returns></returns>
/// <remarks></remarks>
/// <author>卜永济</author>
public static DataTable ImportByPage(string strFileName, int pageSize, int currentPage, ref int total)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = default(HSSFWorkbook);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
total = sheet.LastRowNum;
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j <= cellCount - 1; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
int firstRow = sheet.FirstRowNum + 1 + (currentPage - 1) * pageSize;
for (int i = firstRow; i <= firstRow + pageSize - 1; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
if (i <= sheet.LastRowNum)
{
for (int j = row.FirstCellNum; j <= cellCount - 1; j++)
{
if (row.GetCell(j) != null)
{
HSSFCell cell = (HSSFCell)row.GetCell(j);
if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
switch (cell.CachedFormulaResultType)
{
case NPOI.SS.UserModel.CellType.String:
dataRow[j] = row.GetCell(j).StringCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
dataRow[j] = row.GetCell(j).NumericCellValue;
break;
default:
dataRow[j] = row.GetCell(j).ToString();
break;
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
}
return(dt);
//dt.Dispose();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream(.xls)
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="headRow">列头行数</param>
private static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText = "", int headRow = 1)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Harry"; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = "Harry"; //填加xls文件最后保存者信息
si.Comments = "Harry"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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(936).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(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int ExcelrowIndex = 0;
int DtRowIndex = 0;//
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (ExcelrowIndex == 65535 || ExcelrowIndex == 0)
{
if (ExcelrowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
#region 无用，准备删除
// HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
// headerRow.HeightInPoints = 25;
// HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// //headStyle.Alignment = HorizontalAlignment.Center;//水平居中
// //headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
// HSSFFont font = (HSSFFont)workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
// ExcelrowIndex++;
#endregion
HSSFCellStyle hssfcellstyle = (HSSFCellStyle)workbook.CreateCellStyle();
if (!string.IsNullOrEmpty(strHeaderText))
{
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref hssfcellstyle, ExcelrowIndex, false);
NewFoot(ref hssfcellstyle, workbook, 15);
headerRow.CreateCell(0).SetCellValue(strHeaderText);
headerRow.GetCell(0).CellStyle = hssfcellstyle;
ExcelrowIndex++;
}
}
#endregion
#region 列头及样式
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
#region 无用，准备删除
//HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//headerRow.HeightInPoints = 25;
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.WrapText = true;//自动换行
////headStyle.Alignment = HorizontalAlignment.Center;//水平居中
//headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.Boldweight = 700;
//headStyle.SetFont(font);
#endregion
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
//headerRow.Dispose();
ExcelrowIndex++;
}
//添加更多列
{
for (int i = 1; i < headRow; i++)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
foreach (DataColumn column in dtSource.Columns)
{
string drValue = row[column].ToString();
headerRow.CreateCell(column.Ordinal).SetCellValue(drValue);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, ((arrColWidth[column.Ordinal] + 1) * 256) > 10000 ? 10000 :
((arrColWidth[column.Ordinal] + 1) * 256)); //宽度10000可自定义
}
ExcelrowIndex++;
}
}
#endregion
}
#endregion
//跳过多行列头情况下已添加过的列
DtRowIndex++;
if (DtRowIndex < headRow)
{
continue;
}
#region 填充内容
#region 无用，准备删除
//HSSFRow dataRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//rowStyle.WrapText = true;//自动换行
//rowStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
#endregion
HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow dataRow = NewHSSFRow(ref sheet, workbook, ref rowStyle, ExcelrowIndex, true, 15);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
}
dataRow.GetCell(column.Ordinal).CellStyle = rowStyle;
}
#endregion
ExcelrowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// 读取模版中的数据
/// </summary>
private void DoMapList()
{
try
{
Template.InitExcelData();
Template.InitCustomFormat();
_templateData = new List <T>();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
if (UploadFileId == null)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请上传模板文件"
});
return;
}
var fa = DC.Set <FileAttachment>().Include(x => x.FileData).Where(x => x.ID == UploadFileId).SingleOrDefault();
hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo);
if (ValidityTemplateType && hssfworkbook.GetSheetAt(1).GetRow(0).Cells[2].ToString() != typeof(T).Name)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "错误的模板"
});
return;
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
//所有ExcelPropety属性
List <ExcelPropety> excelPropetys = new List <ExcelPropety>();
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template);
excelPropetys.Add(ep);
}
#region 验证模版正确性 add by dufei
//取得列数
int columnCount = excelPropetys.Count;
//int excelPropetyCount = excelPropetys.Count;
var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault();
if (dynamicColumn != null)
{
columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1;
//excelPropetyCount = excelPropetyCount + dynamicColumn.DynamicColumns.Count - 1;
}
int pIndex = 0;
var cells = sheet.GetRow(0).Cells;
if (columnCount != cells.Count)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
return;
}
else
{
for (int i = 0; i < cells.Count; i++)
{
if (excelPropetys[pIndex].DataType != ColumnDataType.Dynamic)
{
if (cells[i].ToString().Trim('*') != excelPropetys[pIndex].ColumnName)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
return;
}
pIndex++;
}
else
{
var listDynamicColumns = excelPropetys[i].DynamicColumns;
int dcCount = listDynamicColumns.Count;
for (int dclIndex = 0; dclIndex < dcCount; dclIndex++)
{
if (cells[i].ToString().Trim('*') != listDynamicColumns[dclIndex].ColumnName)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
break;
}
i = i + 1;
}
i = i - 1;
pIndex++;
}
}
}
#endregion
int rowIndex = 2;
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
if (IsEmptyRow(row, columnCount))
{
return;
}
T result = new T();
int propetyIndex = 0;
for (int i = 0; i < columnCount; i++)
{
ExcelPropety excelPropety = CopyExcelPropety(excelPropetys[propetyIndex]); //excelPropetys[propetyIndex];
var pts = propetys[propetyIndex];
string value = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
if (excelPropety.DataType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
//验证数据类型并添加错误信息
excelPropety.DynamicColumns[dynamicColIndex].ValueValidity(row.GetCell(i + dynamicColIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(), ErrorListVM.EntityList, rowIndex);
}
i = i + dynamicColCount - 1;
}
else
{
excelPropety.ValueValidity(value, ErrorListVM.EntityList, rowIndex);
}
if (ErrorListVM.EntityList.Count == 0)
{
pts.SetValue(result, excelPropety);
}
propetyIndex++;
}
result.ExcelIndex = rowIndex;
_templateData.Add(result);
rowIndex++;
}
return;
}
catch
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = "请下载新模板或上传符合当前功能的模板"
});
//ErrorListVM.ErrorList.Add(new ErrorMessage { Message = ex.Message });
}
return;
}
/// <summary>
/// 设置单元格是否居中对齐，默认居左和底部
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="vertical">是否垂直居中</param>
/// <param name="horizontal">是否水平居中</param>
/// <param name="wb"></param>
public static void SetCellAlignmentCenter(int row, int col, bool vertical, bool horizontal, ref HSSFWorkbook wb)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
}
}
//private static WriteLog wl.WriteLogs(ex.ToString()); = new WriteLog();
#region 从datatable中将数据导出到excel
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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(936).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(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
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);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
HSSFCellStyle infoStyle = workbook.CreateCellStyle() as HSSFCellStyle;
infoStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont infoFont = workbook.CreateFont() as HSSFFont;
infoFont.FontHeightInPoints = 9;
infoFont.Boldweight = 700;
infoStyle.SetFont(infoFont);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
dataRow.GetCell(column.Ordinal).CellStyle = infoStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
//double result;
//if (isNumeric(drValue, out result))
//{
// double.TryParse(drValue, out result);
// newCell.SetCellValue(result);
// break;
//}
//else
//{
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;
//sheet.Dispose();
//workbook.Dispose();
return(ms);
}
}
/// <summary>
/// 设置单元格的字体的颜色和大小和字体格式
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="color">颜色</param>
/// <param name="size">大小</param>
/// <param name="wb"></param>
public static void SetCellFont(int row, int col, CellFontColor color, CellFontSize size, CellFontName fontName, ref HSSFWorkbook wb)
{
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
ICellStyle Style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
switch (color)
{
case CellFontColor.black: font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
break;
case CellFontColor.blue: font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
break;
case CellFontColor.green: font.Color = NPOI.HSSF.Util.HSSFColor.Green.Index;
break;
case CellFontColor.red: font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
break;
case CellFontColor.white: font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
break;
case CellFontColor.yellow: font.Color = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
break;
}
switch (size)
{
case CellFontSize.s10: font.FontHeightInPoints = 10;
break;
case CellFontSize.s11: font.FontHeightInPoints = 11;
break;
case CellFontSize.s12: font.FontHeightInPoints = 12;
break;
case CellFontSize.s14: font.FontHeightInPoints = 14;
break;
case CellFontSize.s16: font.FontHeightInPoints = 16;
break;
case CellFontSize.s18: font.FontHeightInPoints = 18;
break;
case CellFontSize.s20: font.FontHeightInPoints = 20;
break;
case CellFontSize.s24: font.FontHeightInPoints = 24;
break;
}
switch (fontName)
{
case CellFontName.SongTi: font.FontName = "宋体"; break;
case CellFontName.TimesNewRoman: font.FontName = "Times New Roman"; break;
}
// font.Boldweight = 700; // 设置粗体
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
Style.SetFont(font);
t_cell.CellStyle = Style;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
Style.SetFont(font);
t_cell.CellStyle = Style;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
Style.SetFont(font);
t_cell.CellStyle = Style;
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="sheetName">工作薄名称</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
///设置工作薄名称
ISheet sheet = workbook.CreateSheet(sheetName);
sheet.TabColorIndex = HSSFColor.Red.Index;
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "北京圣邦天麒科技有限公司";
dsi.Category = "业务导出";///类别
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "圣邦天麒技术支持部"; //填加xls文件作者信息
si.ApplicationName = "代理商服务平台"; //填加xls文件创建程序信息
si.LastAuthor = "圣邦天麒技术支持部"; //填加xls文件最后保存者信息
si.Comments = "如有疑问请询问在线客服"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = "圣邦天麒在线系统业务导出"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
///时间格式化格式
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).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(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
//{
// HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
// headStyle.Alignment = HorizontalAlignment.CENTER;
// HSSFFont font = workbook.CreateFont() as HSSFFont;
// font.FontHeightInPoints = 20;
// font.Boldweight = 200;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//}
#endregion
#region 列头及样式
{
//杨宝帅8.23修改把列头提到第一行
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
headStyle.SetFont(font);
//设置每列的文字杨宝帅8-15修改
string[] header = strHeaderText.Split(',');
int i = 0;
foreach (DataColumn column in dtSource.Columns)
{
if (i < header.Length)
{
//headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.CreateCell(column.Ordinal).SetCellValue(header[i]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
i++;
}
//让列头不动
//sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
sheet.CreateFreezePane(0, 1, 0, dtSource.Columns.Count - 1);
}
#endregion
//修改把内容提到第二行
// rowIndex = 2;
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
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>
/// 设置单元格的格式，如日期、小数
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="format"></param>
/// <param name="wb"></param>
public static void SetCellFormat(int row, int col, CellFormat format, ref HSSFWorkbook wb)
{
HSSFCellStyle Style = (HSSFCellStyle)wb.CreateCellStyle();
IDataFormat dataFormat = wb.CreateDataFormat();
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
}
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="exceltype">是否模板</param>
/// <param name="FileName">文件名称</param>
public void DataTableToExcelTemplet(DataTable dtSource, string ExportType, string FileName = "")
{
string TempletFileName = "";//模板文件名称
FileStream file = null;
NPOI.SS.UserModel.IWorkbook workbook = null;
SaveFileDialog savefd = new SaveFileDialog();
//模板文件
TempletFileName = Application.StartupPath + "\\JNMLTemp.xls";
file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
if (FileName != "")
{
savefd.FileName = FileName + ".xls";
}
savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
if (savefd.FilterIndex == 1)
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
}
else
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet("Sheet1");
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 1;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.GetRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.GetCell(column.Ordinal);
string drValue = row[column].ToString();
if (column.ColumnName == "numxh")
{
newCell.SetCellValue(rowIndex.ToString());
continue;
}
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
sheet = null;
using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
if (file != null)
{
file.Dispose();
file.Close();
}
}
/// <summary>
/// NPOI DataGridView 导出 EXCEL
/// </summary>
/// <param name="fileName"> 默认保存文件名</param>
/// <param name="dgv">DataGridView</param>
/// <param name="fontname">字体名称</param>
/// <param name="fontsize">字体大小</param>
public void ExportExcel(string fileName, DataGridView dgv, string fontname, short fontsize, string filename)
{
//检测是否有数据
//if (dgv.SelectedRows.Count == 0) return;
//创建主要对象
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(filename);
//设置字体，大小，对齐方式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = fontname;
font.FontHeightInPoints = fontsize;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
//添加表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
//dataRow = (HSSFRow)sheet.CreateRow(0);
dataRow.CreateCell(0).SetCellValue(filename);
dataRow = (HSSFRow)sheet.CreateRow(1);
for (int i = 0; i < dgv.Columns.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.GetCell(i).CellStyle = style;
}
//注释的这行是设置筛选的
//sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count));
//添加列及内容
for (int i = 0; i < dgv.Rows.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 2);
for (int j = 0; j < dgv.Columns.Count; j++)
{
string ValueType = dgv.Rows[i].Cells[j].Value is null ? "System.String" : dgv.Rows[i].Cells[j].Value.GetType().ToString();
string Value = dgv.Rows[i].Cells[j].Value is null ? "" : dgv.Rows[i].Cells[j].Value.ToString();
switch (ValueType)
{
case "System.String": //字符串类型
dataRow.CreateCell(j).SetCellValue(Value);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(Value, out dateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(Value, out boolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(Value, out intV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(Value, out doubV);
dataRow.CreateCell(j).SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
dataRow.CreateCell(j).SetCellValue("");
break;
default:
dataRow.CreateCell(j).SetCellValue("");
break;
}
dataRow.GetCell(j).CellStyle = style;
//设置宽度
var maxColumn = dgv.Columns.Count;
//列宽自适应，只对英文和数字有效
//for (int j = 0; j <= maxColumn; j++)
//{
// sheet.AutoSizeColumn(j);
//}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= maxColumn; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
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);
}
// sheet.SetColumnWidth(j, (Value.Length) * 512);
}
}
//保存文件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
MemoryStream ms = new MemoryStream();
if (true || saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName + "日语生词" + ".xls";
if (saveFileName.IndexOf("/") > 0)
{
// saveFileName=saveFileName.Insert(saveFileName.IndexOf("/"), @"\");
saveFileName = saveFileName.Replace("/", "-");
}
//if (!CheckFiles(saveFileName))
//{
// MessageBox.Show("文件被站用，请关闭文件后重新进行导出操作 " + saveFileName);
// workbook = null;
// ms.Close();
// ms.Dispose();
// return;
//}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK);
//if (MessageBox.Show("导出成功,点击 [是] 后打开文件所在位置", "导出成功", MessageBoxButtons.YesNo) == DialogResult.Yes)
// dateTabletoCSV.ClickOpenLocation(saveFileName);
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
}
/// <summary>导出EXCEL单表双表</summary>
public static MemoryStream ExportDT(THOK.NPOI.Models.ExportParam ep)
{
#region 变量
string exportDate = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
double columnWidth = ep.ColHeadSize - 9;
short printHeight = 0;
short printWidth = 10;
int sheetCount = 65500; //一个Sheet中最多存65536行数据
int page = 0;
#endregion
#region 浏览器下载
THOK.NPOI.Common.ExportExcelHeper.BrowserLoad(ep.HeadTitle1);
#endregion
#region 创建工作表
workbook = new HSSFWorkbook();
HSSFSheet sheet = null;
#endregion
#region 创建样式
HSSFCellStyle contentDateStyle = workbook.CreateCellStyle() as HSSFCellStyle; //内容时间 单元格
HSSFCellStyle styleHead = workbook.CreateCellStyle() as HSSFCellStyle; //大标题 单元格
HSSFFont fontHead = workbook.CreateFont() as HSSFFont; //大标题 字体
HSSFCellStyle styleDate = workbook.CreateCellStyle() as HSSFCellStyle; //导出时间 单元格
HSSFCellStyle styleColHead = workbook.CreateCellStyle() as HSSFCellStyle; //列头 单元格
HSSFFont fontColHead = workbook.CreateFont() as HSSFFont; //列头 字体
HSSFCellStyle contentStyle = workbook.CreateCellStyle() as HSSFCellStyle; //内容 单元格
HSSFFont fontContent = workbook.CreateFont() as HSSFFont; //内容 字体
contentStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //内容 设置所有列整型格式，也可以通过数据访问层来改变内容格式
HSSFCellStyle contentStyleDailyBalance = workbook.CreateCellStyle() as HSSFCellStyle; //特殊模块 单元格
HSSFFont fontDailyBalance = workbook.CreateFont() as HSSFFont; //特殊模块 字体
#endregion
#region 全局样式
HSSFCellStyle headStyle = GetTitleStyle(ep.BigHeadFont, ep.BigHeadSize, ep.BigHeadColor, styleHead, fontHead);
HSSFCellStyle dateStyle = GetExportDate(styleDate);
HSSFCellStyle colHeadStyle = GetColumnStyle(ep.ColHeadFont, ep.ColHeadSize, ep.ColHeadColor, ep.ColHeadBorder, styleColHead, fontColHead);
#endregion
#region 取得列宽
int[] arrColWidth1 = new int[0];
int[] arrColWidth2 = new int[0];
if (ep.DT1 != null && ep.HeadTitle1 != null)
{
arrColWidth1 = new int[ep.DT1.Columns.Count];
GetColumnWidth(ep.DT1, arrColWidth1);
}
if (ep.DT2 != null && ep.HeadTitle2 != null)
{
arrColWidth2 = new int[ep.DT2.Columns.Count];
GetColumnWidth(ep.DT2, arrColWidth2);
}
#endregion
#region 创建EXCEL 表一
if (ep.DT1 != null && ep.HeadTitle1 != null)
{
int dt1count = ep.DT1.Rows.Count;
#region 判断多少页
if (dt1count % sheetCount == 0)
{
page = dt1count / sheetCount;
}
else
{
page = dt1count / sheetCount + 1;
}
#endregion
for (int a = 0; a < page; a++)
{
#region SHEET分页标题
string sheetNum = a.ToString();
if (a == 0)
{
sheetNum = sheetNum.Substring(0, a.ToString().Length - 1);
}
string headTitle1_sheetNum = ep.HeadTitle1 + sheetNum;
sheet = workbook.CreateSheet(headTitle1_sheetNum) as HSSFSheet;
#endregion
#region SHEET打印设置
sheet.PrintSetup.FitHeight = printHeight;
sheet.PrintSetup.FitWidth = printWidth;
#endregion
int rowIndex1 = 0;
#region 内容分页
DataTable newdt1 = THOK.NPOI.Common.ExportExcelHeper.SetPage(ep.DT1, a + 1, sheetCount);
#endregion
#region 填充数据
foreach (DataRow row in newdt1.Rows)
{
if (rowIndex1 == 0)
{
if (rowIndex1 != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
sheet.PrintSetup.FitHeight = printHeight;
sheet.PrintSetup.FitWidth = printWidth;
}
#region 填充“表头”和它的样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = Convert.ToInt16(ep.BigHeadSize * 1.4);
headerRow.CreateCell(0).SetCellValue(ep.HeadTitle1);
headerRow.GetCell(0).CellStyle = headStyle;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, newdt1.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.BigHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);//给合并的画线
}
}
#endregion
#region 填充“导出时间”和它的样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
headerRow.CreateCell(0).SetCellValue(exportDate);
headerRow.GetCell(0).CellStyle = dateStyle;
CellRangeAddress region = new CellRangeAddress(1, 1, 0, newdt1.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.ColHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);
}
}
#endregion
#region 填充“列头”和它的样式
{
HSSFRow headerRow = sheet.CreateRow(2) as HSSFRow;
headerRow.HeightInPoints = Convert.ToInt16(ep.ColHeadSize * 1.4);
foreach (DataColumn column in newdt1.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = colHeadStyle;
sheet.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth1[column.Ordinal] + columnWidth) * 256));//设置列宽
}
}
#endregion
rowIndex1 = 3;
}
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex1) as HSSFRow;
foreach (DataColumn column in newdt1.Columns)
{
FillContent(dataRow, column, row, contentStyle, fontContent
, contentDateStyle
, contentStyleDailyBalance, fontDailyBalance
, ep
, sheet, headTitle1_sheetNum);
}
rowIndex1++;
#endregion
}
#endregion
}
}
#endregion
#region 创建EXCEL 表二
if (ep.DT2 != null && ep.HeadTitle2 != null)
{
int dt2count = ep.DT2.Rows.Count;
if (dt2count % sheetCount == 0)
{
page = dt2count / sheetCount;
}
else
{
page = dt2count / sheetCount + 1;
}
for (int a = 0; a < page; a++)
{
int rowIndex2 = 0;
DataTable newdt2 = THOK.NPOI.Common.ExportExcelHeper.SetPage(ep.DT2, a + 1, sheetCount);
string strA = a.ToString();
if (a == 0)
{
strA = strA.Substring(0, a.ToString().Length - 1);
}
string headText2strA = ep.HeadTitle2 + strA;
#region 填充数据
foreach (DataRow row in newdt2.Rows)
{
if (rowIndex2 == 0)
{
HSSFRow headerRow;
if (rowIndex2 != 1)
{
sheet = workbook.CreateSheet(headText2strA) as HSSFSheet;
sheet.PrintSetup.FitHeight = printHeight;
sheet.PrintSetup.FitWidth = printWidth;
}
#region 填充表头、样式
{
headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = Convert.ToInt16(ep.BigHeadSize * 1.4);
headerRow.CreateCell(0).SetCellValue(ep.HeadTitle2);
headerRow.GetCell(0).CellStyle = headStyle;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, newdt2.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.BigHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);
}
}
#endregion
#region 导出时间、样式
{
headerRow = sheet.CreateRow(1) as HSSFRow;
headerRow.CreateCell(0).SetCellValue(exportDate);
headerRow.GetCell(0).CellStyle = dateStyle;
CellRangeAddress region = new CellRangeAddress(1, 1, 0, newdt2.Columns.Count - 1);
sheet.AddMergedRegion(region);
if (ep.ColHeadBorder == true)
{
sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);
}
}
#endregion
#region 填充列头、样式
{
headerRow = sheet.CreateRow(2) as HSSFRow;
foreach (DataColumn column in newdt2.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = colHeadStyle;
sheet.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth2[column.Ordinal] + columnWidth) * 256));
}
}
rowIndex2 = 3;
#endregion
}
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex2) as HSSFRow;
foreach (DataColumn column in newdt2.Columns)
{
FillContent(dataRow, column, row, contentStyle, fontContent
, contentDateStyle
, contentStyleDailyBalance, fontDailyBalance
, ep
, sheet, headText2strA);
}
rowIndex2++;
#endregion
}
#endregion
}
}
#endregion
#region 页眉 页脚
sheet.Header.Left = ep.HeaderFooter.ToString();
sheet.Header.Center = ep.HeaderFooter[1].ToString();
sheet.Header.Right = ep.HeaderFooter[2].ToString();
sheet.Footer.Left = ep.HeaderFooter[3].ToString();
sheet.Footer.Center = ep.HeaderFooter[4].ToString();
sheet.Footer.Right = ep.HeaderFooter[5].ToString();
#endregion
#region 返回内存流
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
#endregion
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <Author>卜永济www.cnblogs.com/BuBu/ 2013-6-7 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//#Region "右击文件 属性信息"
if (true)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Your Company";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "卜永济";
//填加xls文件作者信息
si.ApplicationName = "NPOI程序导出";
//填加xls文件创建程序信息
si.LastAuthor = "卜永济";
//填加xls文件最后保存者信息
si.Comments = "说明信息";
//填加xls文件作者信息
si.Title = "CRM";
//填加xls文件标题信息
si.Subject = "NPOI测试Demo";
//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
//#End Region
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i <= dtSource.Rows.Count - 1; i++)
{
for (int j = 0; j <= dtSource.Columns.Count - 1; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
//#Region "新建表，填充表头，填充列头，样式"
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
//#Region "表头及样式"
if (!string.IsNullOrEmpty(strHeaderText))
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(New Region(0, 0, 0, dtSource.Columns.Count - 1))
//headerRow.Dispose()
}
//#End Region
if (string.IsNullOrEmpty(strHeaderText))
{
rowIndex = 0;
}
else
{
rowIndex = 1;
}
//#Region "列头及样式"
if (true)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
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);
}
//headerRow.Dispose()
}
//#End Region
if (string.IsNullOrEmpty(strHeaderText))
{
rowIndex = 1;
}
else
{
rowIndex = 2;
}
}
//#End Region
//#Region "填充内容"
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String":
//字符串类型
newCell.SetCellValue(drValue);
break; // TODO: might not be correct. Was : Exit Select
case "System.DateTime":
//日期类型
DateTime dateV = default(DateTime);
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;
//格式化显示
break; // TODO: might not be correct. Was : Exit Select
case "System.Boolean":
//布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break; // TODO: might not be correct. Was : Exit Select
//整型
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break; // TODO: might not be correct. Was : Exit Select
//浮点型
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break; // TODO: might not be correct. Was : Exit Select
case "System.DBNull":
//空值处理
newCell.SetCellValue("");
break; // TODO: might not be correct. Was : Exit Select
default:
newCell.SetCellValue("");
break; // TODO: might not be correct. Was : Exit Select
}
}
//#End Region
rowIndex += 1;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose()
//workbook.Dispose()
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="myDgv">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[myDgv.Columns.Count];
foreach (DataGridViewColumn item in myDgv.Columns)
{
arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length;
}
for (int i = 0; i < myDgv.Rows.Count; i++)
{
for (int j = 0; j < myDgv.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataGridViewRow row in myDgv.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataGridViewColumn column in myDgv.Columns)
{
headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
headerRow.GetCell(column.Index).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
if (row.Index > 0)
{
foreach (DataGridViewColumn column in myDgv.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index);
string drValue = myDgv[column.Index, row.Index - 1].Value.ToString();
switch (column.ValueType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
}
}
}
else
{
rowIndex--;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// 将DataTable导出到Excel，返回文件的web路径
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">文件名</param>
/// <returns></returns>
public static string ExportDataTableToExcel(DataTable dt, string fileName)
{
#region 表头
var hssfworkbook = new HSSFWorkbook();
HSSFSheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 20;
hssfSheet.SetColumnWidth(0, 35 * 256);
hssfSheet.SetColumnWidth(3, 20 * 256);
// 表头
HSSFRow tagRow = hssfSheet.CreateRow(0);
tagRow.Height = 22 * 20;
// 标题样式
HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
cellStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
cellStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
cellStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
cellStyle.BottomBorderColor = HSSFColor.BLACK.index;
cellStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
cellStyle.LeftBorderColor = HSSFColor.BLACK.index;
cellStyle.BorderRight = HSSFCellStyle.BORDER_THIN;
cellStyle.RightBorderColor = HSSFColor.BLACK.index;
cellStyle.BorderTop = HSSFCellStyle.BORDER_THIN;
cellStyle.TopBorderColor = HSSFColor.BLACK.index;
//NPOI.SS.UserModel.Font font = hssfworkbook.CreateFont();
//font.Boldweight = 30 * 20;
//font.FontHeight = 12 * 20;
//cellStyle.SetFont(font);
int colIndex;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
tagRow.CreateCell(colIndex).SetCellValue(dt.Columns[colIndex].ColumnName);
tagRow.GetCell(colIndex).CellStyle = cellStyle;
}
#endregion
#region 表数据
// 表数据
for (int k = 0; k < dt.Rows.Count; k++)
{
DataRow dr = dt.Rows[k];
HSSFRow row = hssfSheet.CreateRow(k + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dr[i].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
}
#endregion
string path = HttpContext.Current.Server.MapPath("~/UploadFiles/Export");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
using (var file = new FileStream(Path.Combine(path, fileName), FileMode.Create))
{
hssfworkbook.Write(file);
file.Close();
}
return(string.Format("{0}UploadFiles/Export/{1}", BasePath, fileName));
}