C#导出excel方法
protected void ExcelExport()
{
string saveName = "Excel_File_" + System.DateTime.Now.ToString("yyyyMMddhhmmssff") + ".xls";
MemoryStream mStream = new MemoryStream();
int rowIndex = 0;
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(saveName);
IRow headerRow;
#region ±í¸ñÑùʽ
HSSFFont fontTitle = (HSSFFont)workbook.CreateFont();
HSSFFont fontHead = (HSSFFont)workbook.CreateFont();
fontTitle = (HSSFFont)workbook.CreateFont();
fontTitle.FontHeightInPoints = 15;
fontTitle.Boldweight = 600;
HSSFCellStyle titleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
titleStyle.SetFont(fontTitle);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
fontHead = (HSSFFont)workbook.CreateFont();
fontHead.FontHeightInPoints = 11;
fontHead.Boldweight = 600;
headStyle.SetFont(fontHead);
HSSFCellStyle tableheadStyle = (HSSFCellStyle)workbook.CreateCellStyle();
fontHead = (HSSFFont)workbook.CreateFont();
fontHead.FontHeightInPoints = 11;
fontHead.Boldweight = 600;
tableheadStyle.SetFont(fontHead);
tableheadStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tableheadStyle.TopBorderColor = HSSFColor.BLACK.index;
tableheadStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tableheadStyle.RightBorderColor = HSSFColor.BLACK.index;
tableheadStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tableheadStyle.BottomBorderColor = HSSFColor.BLACK.index;
tableheadStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tableheadStyle.LeftBorderColor = HSSFColor.BLACK.index;
HSSFCellStyle tablerowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
tablerowStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tablerowStyle.TopBorderColor = HSSFColor.BLACK.index;
tablerowStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tablerowStyle.RightBorderColor = HSSFColor.BLACK.index;
tablerowStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tablerowStyle.BottomBorderColor = HSSFColor.BLACK.index;
tablerowStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
tablerowStyle.LeftBorderColor = HSSFColor.BLACK.index;
#endregion
//title
headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
string title = GetLocalResourceObject("ReportTitle").ToString() + " " + FormatDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
headerRow.CreateCell(0).SetCellValue(title);
headerRow.GetCell(0).CellStyle = titleStyle;
//±êÌâºÏ²¢µ¥Ôª¸ñ
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, 0, ASP.DBSApplication.DBSConditionDis.DisplayList.Length + 2 + 4 - 1);//DisplayListÖÐËùÓÐÏî+µÚÒ»ÁÐͼÏñºÅ+µÚ¶þÁÐɨÃèʱ¼ä+×îºóÒ»Áнá¹ûÁУ¨4£©-1£¨ÒòΪ´Ó0¿ªÊ¼£©
sheet.AddMergedRegion(cellRangeAddress);
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);//¼Ó¿ÕÐÐ
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);//¼Ó¿ÕÐÐ
int searchConditionIndex_f = 0;//ÿÐеÚÒ»¸ö²éѯÌõ¼þËùÔÚµÄλÖÃ
int searchResultIndex_f = 1; //ÿÐеÚÒ»¸ö²éѯ½á¹ûËùÔÚµÄλÖÃ
int searchConditionIndex_s = 2;//ÿÐеڶþ¸ö²éѯÌõ¼þËùÔÚµÄλÖÃ
int searchResultIndex_s = 3;//ÿÐеڶþ¸ö²éѯ½á¹ûËùÔÚµÄλÖÃ
if (encode == "rtl")
{
searchConditionIndex_f = 3;
searchResultIndex_f = 2;
searchConditionIndex_s = 1;
searchResultIndex_s = 0;
}
//ÏÔʾ²éѯÌõ¼þ
if (cbxDate.Checked)//ʱ¼ä
{
if (headerRow.GetCell(searchConditionIndex_f) != null)
{
headerRow.CreateCell(searchConditionIndex_s).SetCellValue(this.cbxDate.Text + ":");
headerRow.CreateCell(searchResultIndex_s).SetCellValue(linkBeginDate.Text + "~" + linkEndDate.Text);
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);
}
else
{
headerRow.CreateCell(searchConditionIndex_f).SetCellValue(this.cbxDate.Text + ":");
headerRow.CreateCell(searchResultIndex_f).SetCellValue(linkBeginDate.Text + "~" + linkEndDate.Text);
}
}
if (cbxInspectionType.Checked)
{
if (headerRow.GetCell(searchConditionIndex_f) != null)
{
headerRow.CreateCell(searchConditionIndex_s).SetCellValue(this.cbxInspectionType.Text + ":");
headerRow.CreateCell(searchResultIndex_s).SetCellValue(listInspectionType.SelectedItem.Text);
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);
}
else
{
headerRow.CreateCell(searchConditionIndex_f).SetCellValue(this.cbxInspectionType.Text + ":");
headerRow.CreateCell(searchResultIndex_f).SetCellValue(listInspectionType.SelectedItem.Text);
}
}
if (cbxInspector.Checked)
{
if (headerRow.GetCell(searchConditionIndex_f) != null)
{
headerRow.CreateCell(searchConditionIndex_s).SetCellValue(this.cbxInspector.Text + ":");
headerRow.CreateCell(searchResultIndex_s).SetCellValue(txtInspector.Text);
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);
}
else
{
headerRow.CreateCell(searchConditionIndex_f).SetCellValue(this.cbxInspector.Text + ":");
headerRow.CreateCell(searchResultIndex_f).SetCellValue(txtInspector.Text);
}
}
if (cbxConclusion.Checked)
{
if (headerRow.GetCell(searchConditionIndex_f) != null)
{
headerRow.CreateCell(searchConditionIndex_s).SetCellValue(this.cbxConclusion.Text + ":");
headerRow.CreateCell(searchResultIndex_s).SetCellValue(txtConclusion.SelectedItem.Text);
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);
}
else
{
headerRow.CreateCell(searchConditionIndex_f).SetCellValue(this.cbxConclusion.Text + ":");
headerRow.CreateCell(searchResultIndex_f).SetCellValue(txtConclusion.SelectedItem.Text);
}
}
if (ASP.DBSApplication.DBSConditionDis.ConditionList.Length > 0)
{
Condition searchCondition;
for (int searchIndex = 0; searchIndex < ASP.DBSApplication.DBSConditionDis.ConditionList.Length; searchIndex++)
{
searchCondition = ASP.DBSApplication.DBSConditionDis.ConditionList[searchIndex];
if (((CheckBox)this.FindControl("dy_cbx_" + searchCondition.FieldName.ToString())).Checked)
{
if (headerRow.GetCell(searchConditionIndex_f) != null)
{
headerRow.CreateCell(searchConditionIndex_s).SetCellValue(GetLocalResourceObject(searchCondition.LabelName).ToString() + ":");
if (searchCondition.ControlType == "TextBox")
{
headerRow.CreateCell(searchResultIndex_s).SetCellValue(((TextBox)this.FindControl("dy_Input_" + searchCondition.FieldName.ToString())).Text);
}
else if (searchCondition.ControlType == "DropDownList")
{
headerRow.CreateCell(searchResultIndex_s).SetCellValue(((DropDownList)this.FindControl(searchCondition.ControlID)).SelectedItem.Text);
}
else if (searchCondition.ControlType == "Calendar")
{
headerRow.CreateCell(searchResultIndex_s).SetCellValue(((LinkButton)this.FindControl(("dy_Input_" + searchCondition.FieldName))).Text);
}
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);
}
else
{
headerRow.CreateCell(searchConditionIndex_f).SetCellValue(GetLocalResourceObject(searchCondition.LabelName).ToString() + ":");
if (searchCondition.ControlType == "TextBox")
{
headerRow.CreateCell(searchResultIndex_f).SetCellValue(((TextBox)this.FindControl("dy_Input_" + searchCondition.FieldName.ToString())).Text);
}
else if (searchCondition.ControlType == "DropDownList")
{
headerRow.CreateCell(searchResultIndex_f).SetCellValue(((DropDownList)this.FindControl(searchCondition.ControlID)).SelectedItem.Text);
}
else if (searchCondition.ControlType == "Calendar")
{
headerRow.CreateCell(searchResultIndex_f).SetCellValue(((LinkButton)this.FindControl(("dy_Input_" + searchCondition.FieldName))).Text);
}
}
}
}
}
//ÏÔʾ²éѯ¸öÊý
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);
string searchCount = GetLocalResourceObject("lbCountResource1.Text").ToString() + this.DataSource.Rows.Count.ToString();
headerRow.CreateCell(0).SetCellValue(searchCount);
headerRow.GetCell(0).CellStyle = headStyle;
//ÏÔʾÿÁбêÌâ
rowIndex++;
headerRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < DataSource.Columns.Count; i++)
{
if (DataSource.Columns[i].ColumnName != "UnitID")
{
if (DataSource.Columns[i].ColumnName == "Scan Image" || DataSource.Columns[i].ColumnName == "Scan Time" || DataSource.Columns[i].ColumnName == "CheckProcess")
{
//Èý¸ö¹Ì¶¨Ïî
headerRow.CreateCell(i).SetCellValue(GetLocalResourceObject("Field_" + DataSource.Columns[i].ColumnName.Trim().Replace(" ", "")).ToString());//¹¹Ôì×ÊÔ´µÄÃû×Ö
headerRow.GetCell(i).CellStyle = tableheadStyle;
for (int index = i + 1; index < i + 4; index++)
{
//ËÄÁÐÖÐʣϵÄÈýÁÐ
headerRow.CreateCell(index);
headerRow.GetCell(index).CellStyle = tableheadStyle;
}
//×îºóÒ»ÁбêÌâºÏ²¢µ¥Ôª¸ñ
CellRangeAddress cellRangeAddress_process = new CellRangeAddress(rowIndex, rowIndex, ASP.DBSApplication.DBSConditionDis.DisplayList.Length + 2, ASP.DBSApplication.DBSConditionDis.DisplayList.Length + 2 + 4 - 1);
sheet.AddMergedRegion(cellRangeAddress_process);
sheet.SetColumnWidth(i, (Encoding.GetEncoding("utf-8").GetBytes(GetLocalResourceObject("Field_" + DataSource.Columns[i].ColumnName.Trim().Replace(" ", "")).ToString()).Length + 3) * 256);
}
else
{
for (int j = 0; j < ASP.DBSApplication.DBSConditionDis.ConditionList.Length; j++)
{
if ("[" + DataSource.Columns[i].ColumnName + "]" == ASP.DBSApplication.DBSConditionDis.ConditionList[j].FieldName)
{
headerRow.CreateCell(i).SetCellValue(GetLocalResourceObject(ASP.DBSApplication.DBSConditionDis.ConditionList[j].LabelName).ToString());//¹¹Ôì×ÊÔ´µÄÃû×Ö
sheet.SetColumnWidth(i, (Encoding.GetEncoding("utf-8").GetBytes(GetLocalResourceObject(ASP.DBSApplication.DBSConditionDis.ConditionList[j].LabelName).ToString()).Length + 3) * 256);
break;
}
}
headerRow.GetCell(i).CellStyle = tableheadStyle;
}
}
}
//ÏÔʾ²éѯ½á¹û
rowIndex++;
string strSQL;
System.Web.UI.WebControls.DataGrid grid = new DataGrid(); //´´½¨ÔÚcheckÖÐÌí¼ÓµÄgrid
foreach (DataRow row in DataSource.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < DataSource.Columns.Count; i++)
{
if (DataSource.Columns[i].ColumnName == "CheckProcess") // ¼Ó¼ì²é¹ý³ÌÁÐ
{
strSQL = m_myIdrDa.BuildSearchSqlForConclusion(row[DataSource.Columns.Count - 1].ToString(), this.hid_InspectionType.Value, this.hid_Inspector.Value, this.hid_Conclusion.Value);
//strSQL = m_myIdrDa.BuildSearchSqlForConclusion(row[DataSource.Columns.Count - 1].ToString(), this.hid_InspectionType.Value, this.hid_Inspector.Value, this.hid_Conclusion.Value, this.BeginDate, this.EndDate);
if (this.FillDataGridForConclusion(grid, strSQL) > 0) //°Ñ²éµ½µÄ¡°²éÑé»·½Ú¡± ¡°²éÑéÔ±¡± ¡°½áÂÛ¡± ¡°Ê±¼ä¡±·Åµ½gridÖÐ
{
//¸ù¾Ý½áÂÛ¸öÊýºÏ²¢Ç°Ãæ²éѯ½á¹ûµÄµ¥Ôª¸ñ
for (int result = 0; result < i; result++)
{
CellRangeAddress cellResultRangeAddress = new CellRangeAddress(rowIndex, rowIndex + grid.Items.Count, result, result);//×îºóÒ»ÁÐÊÇtable£¬ÓÉÒ»ÁбäÎåÁеĵ¥Ôª¸ñ£¬Ôö¼ÓËÄÁÐ
sheet.AddMergedRegion(cellResultRangeAddress);
}
dataRow.CreateCell(i).SetCellValue(GetLocalResourceObject("Field_InspectionAppID").ToString());
dataRow.CreateCell(i + 1).SetCellValue(GetLocalResourceObject("cbxInspectorResource1.Text").ToString());
dataRow.CreateCell(i + 2).SetCellValue(GetLocalResourceObject("cbxConclusionResource1.Text").ToString());
dataRow.CreateCell(i + 3).SetCellValue(GetLocalResourceObject("Field_Time").ToString());
//ÄÚ²¿½áÂÛ×Ó±íµÄ±íÍ·
dataRow.GetCell(i).CellStyle = tableheadStyle;
dataRow.GetCell(i + 1).CellStyle = tableheadStyle;
dataRow.GetCell(i + 2).CellStyle = tableheadStyle;
dataRow.GetCell(i + 3).CellStyle = tableheadStyle;
//ÉèÖòéÑé»·½Ú£¬¼ì²éÔ±£¬½áÂÛ£¬Ê±¼äµÄ¿í¶È
for (int inner = 0; inner < grid.Items.Count; inner++)
{
IRow innerDataRow = sheet.CreateRow(rowIndex + inner + 1);
// dataRow.GetCell(i).CellStyle = tablerowStyle;
for (int j = 0; j < 4; j++)
{
//ºº»¯ "ͼ¼ì½áÂÛ" ºÍ ¡°Í¼¼ìÁ÷³Ì¡±
grid.Items[inner].Cells[2].Text = grid.Items[inner].Cells[2].Text.Replace("No Suspect", GetGlobalResourceObject("GlobalDBS", "Text_NoSuspect").ToString());
grid.Items[inner].Cells[2].Text = grid.Items[inner].Cells[2].Text.Replace("With Suspect", GetGlobalResourceObject("GlobalDBS", "Text_WithSuspect").ToString());
grid.Items[inner].Cells[2].Text = grid.Items[inner].Cells[2].Text.Replace("Pending", GetGlobalResourceObject("GlobalDBS", "Text_Pending").ToString());
//ºº»¯¼ì²é¹ý³Ì
grid.Items[inner].Cells[0].Text = grid.Items[inner].Cells[0].Text.Replace("mancheck", GetGlobalResourceObject("GlobalDBS", "Text_Mancheck").ToString());
grid.Items[inner].Cells[0].Text = grid.Items[inner].Cells[0].Text.Replace("checkout", GetGlobalResourceObject("GlobalDBS", "Text_Checkout").ToString());
grid.Items[inner].Cells[0].Text = grid.Items[inner].Cells[0].Text.Replace("recheck", GetGlobalResourceObject("GlobalDBS", "Text_recheck").ToString());
grid.Items[inner].Cells[0].Text = grid.Items[inner].Cells[0].Text.Replace("check", GetGlobalResourceObject("GlobalDBS", "Text_Check").ToString());
try
{
grid.Items[inner].Cells[3].Text = FormatDateTime(grid.Items[inner].Cells[3].Text);
}
catch
{
}
innerDataRow.CreateCell(i + j).SetCellValue(grid.Items[inner].Cells[j].Text);
innerDataRow.GetCell(i + j).CellStyle = tablerowStyle;
sheet.SetColumnWidth(i + j, 4500);
}
//½«Ç°Ãæ²éѯ½á¹ûµÄµ¥Ôª¸ñ¼Ó±ß¿ò
for (int index = 0; index < i; index++)
{
innerDataRow.CreateCell(index);
innerDataRow.GetCell(index).CellStyle = tablerowStyle;
}
}
rowIndex += grid.Items.Count;
}
}
else
{
if (DataSource.Columns[i].ColumnName != "UnitID")
{
if (DataSource.Columns[i].ColumnName == "Scan Image")
{
dbs.Model.ScanImgInfo img = m_myIdrDa.GetScanImageByImageId(row[i].ToString());
string imgSrc = MdstRootPath + img.Path + img.Id + strSuffix;
try
{
byte[] byteArrayOut = GetByteImage(imgSrc);
//anchorÖ÷ÒªÓÃÓÚÉèÖÃͼƬµÄÊôÐÔ
HSSFClientAnchor anchor = new HSSFClientAnchor(5, 5, 15, 15, 0, rowIndex, 1, rowIndex + 1);
//²åÈëͼƬ
IDrawing patriarch = sheet.CreateDrawingPatriarch();
patriarch.CreatePicture(anchor, workbook.AddPicture(byteArrayOut, PictureType.JPEG));
}
catch
{
}
dataRow.CreateCell(i).SetCellValue(img.Id);
dataRow.Height = 600;//ÉèÖÃÐиßΪ800£¬ÊÊÓ¦ËõÂÔͼ
}
else if (DataSource.Columns[i].ColumnName == "Scan Time")
{
try
{
dataRow.CreateCell(i).SetCellValue(FormatDateTime(row[i].ToString()));
}
catch
{
dataRow.CreateCell(i).SetCellValue(row[i].ToString());
}
}
else
{
//if (i == int.Parse(this.hid_GammaIndex.Value) || i == int.Parse(this.hid_NeutronIndex.Value))//ÓÃÓÚRM±¨¾¯
//{
// if (row[i].ToString() == "yes")
// {
// dataRow.CreateCell(i).SetCellValue(GetLocalResourceObject("RM_CONTENT_YES").ToString());
// }
// else if (row[i].ToString() == "no")
// {
// dataRow.CreateCell(i).SetCellValue(GetLocalResourceObject("RM_CONTENT_NO").ToString());
// }
// else
// {
// dataRow.CreateCell(i);
// }
//}
//else
{
dataRow.CreateCell(i).SetCellValue(row[i].ToString());
}
}
dataRow.GetCell(i).CellStyle = tablerowStyle;
}
}
}
rowIndex++;
}
////×Ô¶¯µ÷ÕûÁпí
//for (int i = 0; i < DataSource.Columns.Count; i++)
//{
// sheet.AutoSizeColumn(i);
//}
workbook.Write(mStream);
mStream.Flush();
mStream.Position = 0;
//Îĵµ±à¼Íêºó£¬ÎÒÊÇͨ¹ýresposneÊä³ö
Response.Clear();
Response.Buffer = true;
Response.Charset = "UTF8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/msword";//ÉèÖÃÊä³öÎļþÀàÐÍΪWORDÎļþ¡£
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveName));
Response.BinaryWrite(mStream.ToArray());
Response.Flush();
Response.End();
}
