/*
* 响应到客户端
*
* Param fileName 文件名
*/
public static void WriteToClient(String fileName, HSSFWorkbook workbook)
{
//Write the stream data of workbook to the root directory
//FileStream file = new FileStream(@"c:/test.xls", FileMode.Create);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Expires = 0;
HttpContext.Current.Response.CacheControl = "no-cache";
HttpContext.Current.Response.ContentType = "application/x-excel";
//inline
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + UTF_FileName(fileName));
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
workbook.Write(HttpContext.Current.Response.OutputStream);
Sheet sheet = workbook.GetSheetAt(0);
sheet = null;
workbook.Dispose();
workbook = null;
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
//file.Close();
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
if (Extension == ".xls")
{
//string notxlsx = ("This is not an xlsx file");
//ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//Counts the number of cells in a row and determines the columns from that.
int counter = sheet.GetRow(0).Cells.Count;
// J < number of columns needs to be exact at this moment
for (int j = 0; j < counter; j++)
{
// set each column to a - ** letters
// dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//Get first row and set the headers for each cell
//dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
//Get each cell value in row 0 and return its string for a column name.
dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
//Hackish way to remove the bad first row made by getting column names
dt.Rows.RemoveAt(0);
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
//Bind the data
GridView1.DataBind();
sheet.Dispose();
hssfworkbook.Dispose();
}
else
{
//Create a new epplus package using openxml
var pck = new OfficeOpenXml.ExcelPackage();
//load the package with the filepath I got from my fileuploader above on the button
//pck.Load(new System.IO.FileInfo(FilePath).OpenRead());
//stream the package
FileStream stream = new FileStream(FilePath, FileMode.Open);
pck.Load(stream);
//So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
//Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
var ws = pck.Workbook.Worksheets[1];
//This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
if (ws == null)
{
ws = pck.Workbook.Worksheets.Add("Sheet1");
// Obiviously I didn't add anything to the sheet so probably can count on it being blank.
}
//I created this datatable for below.
DataTable tbl = new DataTable();
//My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
var hdr = bool.Parse(isHDR);
Console.WriteLine(hdr);
//Set the bool value for from above.
var hasHeader = hdr;
//Setup the table based on the value from my bool
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
tbl.Rows.Add(row);
}
//Bind Data to GridView
//I have all my info in the tbl dataTable so the datasource for the Gridview1 is set to tbl
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = tbl;
//Bind the data
GridView1.DataBind();
pck.Save();
pck.Dispose();
stream.Close();
// string pathD = FilePath;
FilePath = null;
stream = null;
// var fileToDelete = new FileInfo(pathD);
// fileToDelete.Delete();
}
}
/*
* 生成文件
*
* Param fileName 文件名
*
* Return 生成文件的URL
*/
public static string WriteToFile(String fileName, HSSFWorkbook workbook)
{
//临时文件路径
string tempFilePath = HttpContext.Current.Server.MapPath("~/" + BusinessConstants.TEMP_FILE_PATH);
string tempFileName = GetRandomFileName(fileName);
if (!Directory.Exists(tempFilePath))
Directory.CreateDirectory(tempFilePath);
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(tempFilePath + tempFileName, FileMode.Create);
workbook.Write(file);
file.Flush();
file.Close();
file.Dispose();
file = null;
Sheet sheet = workbook.GetSheetAt(0);
sheet = null;
workbook.Dispose();
workbook = null;
return GetShowFileUrl(tempFileName);
}
private static void WriteXLS(string pathTemplate, List<TempTransfer> listTransfers, Report reportTo)
{
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory),"Targetfile" + DateTime.Now.ToString("yyy_MM_dd_hh_mm") + ".xls");
int row = reportTo.ResultRow;
HSSFRow currentRow;
HSSFCell currentCell;
using (FileStream fs = new FileStream(pathTemplate, FileMode.Open, FileAccess.Read))
{
//getting complete workbook
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
// Getting the worksheet
HSSFSheet sheet = templateWorkbook.GetSheet(reportTo.SheetName) as HSSFSheet;
foreach (TempTransfer item in listTransfers.OrderBy(t => t.RowNumber).ThenBy(t => t.ColIndex))
{
currentRow = sheet.GetRow(Convert.ToInt32(reportTo.ResultRow - 1 + item.RowNumber - 1)) as HSSFRow;
//check if row exists
if (currentRow == null)
{
currentRow = sheet.CreateRow(Convert.ToInt32(reportTo.ResultRow - 1 + item.RowNumber - 1)) as HSSFRow;
}
currentCell = currentRow.GetCell(item.ColIndex - 1) as HSSFCell;
if (currentCell == null)
{
currentCell = currentRow.CreateCell(item.ColIndex - 1) as HSSFCell;
}
//hardcoded for the LIMS Template
//need to make it more generic
if (!item.ColIndex.Equals(8))
{
//Not a UWI
currentCell.SetCellValue(item.Value);
}
else
{
if (Regex.IsMatch(item.Value.ToUpper(), @"\d{2,3}/\d{2}-\d{2}-\d{3}-\d{2}W\d/\d{2}"))
{
Dictionary<string, string> uwi = UWI.ParseUWIAlberta(item.Value);
currentCell.SetCellValue(uwi["wellIdent"]);
currentRow.CreateCell(8).SetCellValue(uwi["wellLegalSub"]);
currentRow.CreateCell(9).SetCellValue(uwi["wellSection"]);
currentRow.CreateCell(10).SetCellValue(uwi["wellTownShip"]);
currentRow.CreateCell(11).SetCellValue(uwi["wellRange"]);
currentRow.CreateCell(12).SetCellValue(uwi["wellMeridian"]);
}
}
}
using (FileStream writer = new FileStream(path, FileMode.Create, FileAccess.Write))
{
templateWorkbook.Write(writer);
}
sheet.Dispose();
templateWorkbook.Dispose();
}
}
//private static WriteLog wl = 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;
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++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
workbook.Dispose();
return ms;
}
}
/// </summary>
/// <param name="dt"> 数据源</param>
/// <returns>stream</returns>
public static Stream StreamData(DataTable dt, string sheetname, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = null;
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
sheet = (HSSFSheet)workbook.CreateSheet(sheetname);
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.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, dt.Columns.Count - 1));
headerRow = null;
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable，将datatable的列名赋予sheet
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
headerRow = null;
}
#endregion
int index = 2; //表头和列头已经占用一行，所以从2开始
foreach (DataRow row in dt.Rows)
{
HSSFRow datarow = (HSSFRow)sheet.CreateRow(index);
foreach (DataColumn column in dt.Columns)
{
// row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet
HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); // 实例化cell
string drValue = row[column].ToString();
if (drValue == null || drValue == "")
{
newCell.SetCellValue("");
continue;
}
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
case "System.DateTime"://日期类型
newCell.SetCellValue(drValue);
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":
case "System.Float":
case "System.Single":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
index++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//headerrow = null;
//workbook = null;
workbook.Dispose();
return ms;
}
/// <summary>
/// NPOI简单Demo，快速入门代码
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
/// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static void ExportEasy(DataTable dtSource, string strFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
//填充表头
Row dataRow = sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//填充内容
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
}
}
//保存
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
workbook.Dispose();
}
public static DataTable Import(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
Sheet sheet = workbook.GetSheet(SheetName);
DataTable table = new DataTable();
Row headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
{
Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
dataRow[j] = row.GetCell(j).ToString();
}
ExcelFileStream.Close();
workbook.Dispose();
return table;
}
/// <summary>
/// 開始匯出資料
/// </summary>
private void Export()
{
SetCondition(cbRegion, lblRegion, false);
SetCondition(cbTechnology, lblTechID, false);
string strRegion = "";
foreach(string s in lblRegion.Text.Trim().Split(','))
{
if (s != "") { strRegion += "," + s; }
}
if (strRegion.Length > 0 && strRegion.Substring(0, 1) == ",") { strRegion = strRegion.Remove(0, 1); }
string strSelCountryID = "";
foreach (string s in lblCountrys.Text.Trim().Split(','))
{
if (s != "") { strSelCountryID += "," + s; }
}
if (strSelCountryID.Length > 0 && strSelCountryID.Substring(0, 1) == ",") { strSelCountryID = strSelCountryID.Remove(0, 1); }
//string strSelCountryID = lblCountrys.Text.Trim();
//if (strSelCountryID.Length > 0 && strSelCountryID.Substring(0, 1) == ",")
//{
// strSelCountryID = strSelCountryID.Remove(0, 1);
//}
//判斷選擇的區域最後是否有逗號
if (strRegion.Length > 0 && strRegion.Substring(strRegion.Length - 1, 1) == ",")
{
strRegion = strRegion.Remove(strRegion.Length - 1, 1);
}
//判斷選擇的國家最後是否有逗號
if (strSelCountryID.Length > 0 && strSelCountryID.Substring(strSelCountryID.Length - 1, 1) == ",")
{
strSelCountryID = strSelCountryID.Remove(strSelCountryID.Length - 1, 1);
}
SqlCommand cmd = new SqlCommand("STP_GetExportData");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RID", strRegion);
cmd.Parameters.AddWithValue("@CID", strSelCountryID);
cmd.Parameters.AddWithValue("@PTID", ddlProductType.SelectedValue);
cmd.Parameters.AddWithValue("@TecID", lblTechID.Text.Trim());
DataSet dsTech = SQLUtil.QueryDS(cmd);
MemoryStream ms = new MemoryStream();
string strTitle = "WoWi_Technology_"
+ DateTime.Now.ToString("yyyyMMdd_HHmmss_")
+ User.Identity.Name;
//Mark By Adams=================================
//base.Response.Clear();
//base.Response.Buffer = true;
//base.Response.Charset = "utf-8";
string strFile = System.Web.HttpUtility.UrlEncode(strTitle + ".xls", System.Text.Encoding.UTF8);
//base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFile);
//base.Response.ContentEncoding = Encoding.GetEncoding("utf-8");
//base.Response.ContentType = "application/ms-excel";
//Mark By Adams=================================
//建立試算表
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet;
IRow row;
//建立欄位名稱儲存格樣式
ICellStyle icsHeader = workbook.CreateCellStyle();
icsHeader.Alignment = HorizontalAlignment.CENTER;
icsHeader.VerticalAlignment = VerticalAlignment.CENTER;
icsHeader.WrapText = true;
icsHeader.FillForegroundColor = HSSFColor.LIGHT_TURQUOISE.index;
icsHeader.FillPattern = FillPatternType.SOLID_FOREGROUND;
icsHeader.BorderBottom = CellBorderType.THIN;
icsHeader.BorderLeft = CellBorderType.THIN;
icsHeader.BorderRight = CellBorderType.THIN;
icsHeader.BorderTop = CellBorderType.THIN;
icsHeader.BottomBorderColor = HSSFColor.BLACK.index;
icsHeader.LeftBorderColor = HSSFColor.BLACK.index;
icsHeader.RightBorderColor = HSSFColor.BLACK.index;
icsHeader.TopBorderColor = HSSFColor.BLACK.index;
IFont iFont = workbook.CreateFont();
iFont.Color = HSSFColor.BLACK.index;
iFont.FontHeightInPoints = 9;
iFont.FontName = "Arial";
iFont.Boldweight = (short)FontBoldWeight.BOLD;
icsHeader.SetFont(iFont);
//建立文字儲存格樣式
ICellStyle icsTxt = workbook.CreateCellStyle();
icsTxt.Alignment = HorizontalAlignment.CENTER;
icsTxt.VerticalAlignment = VerticalAlignment.CENTER;
icsTxt.WrapText = true;
icsTxt.BorderBottom = CellBorderType.THIN;
icsTxt.BorderLeft = CellBorderType.THIN;
icsTxt.BorderRight = CellBorderType.THIN;
icsTxt.BorderTop = CellBorderType.THIN;
icsTxt.BottomBorderColor = HSSFColor.BLACK.index;
icsTxt.LeftBorderColor = HSSFColor.BLACK.index;
icsTxt.RightBorderColor = HSSFColor.BLACK.index;
icsTxt.TopBorderColor = HSSFColor.BLACK.index;
IFont iFont1 = workbook.CreateFont();
iFont1.FontHeightInPoints = 9;
iFont1.FontName = "Arial";
icsTxt.SetFont(iFont1);
//判斷第16個模組是否有勾選匯出
//bool blnFrequency = chExportData.Items[chExportData.Items.Count - 1].Selected;
bool blnFrequency;
//選擇匯出的Technology
foreach (ListItem liTechnology in cbTechnology.Items)
{
if (liTechnology.Selected)
{
blnFrequency = ((CheckBoxList)gvExportData.Rows[gvExportData.Rows.Count - 1].FindControl("chExportData")).Items[0].Selected;
sheet = workbook.CreateSheet(liTechnology.Text.Replace("/", " "));
row = sheet.CreateRow(0);
//建立表頭
int j = 0;
string strExportData = "";
string strValue = "";
//foreach (ListItem liExportData in chExportData.Items)
//{
// if (liExportData.Selected && liExportData.Value != "0")
// {
// //記錄勾選要匯出的欄位
// strExportData += "；" + liExportData.Value;
// ICell cell;
// if (j == 0)
// {
// cell = row.CreateCell(j);
// strValue = "No.";
// cell.SetCellValue(strValue);
// cell.CellStyle = icsHeader;
// //自動設定欄位寬度
// sheet.AutoSizeColumn(j);
// j++;
// }
// cell = row.CreateCell(j);
// strValue = liExportData.Value.Split('／')[2].ToString().Replace("_", " ").Replace("$", "/").Replace("@", ".").Replace("#td#", "-").Replace("#thl#", "(").Replace("#thr#", ")").Replace("#a#", "&");
// row.HeightInPoints = 24;
// if (strValue.Contains("#br#"))
// {
// icsHeader.WrapText = true;
// strValue = strValue.Replace("#br#", "\n");
// //因為換行所以將Row的高度變成4倍
// //row.HeightInPoints = 2 * sheet.DefaultRowHeight / 20;
// //row.HeightInPoints = 24;
// }
// else { icsHeader.WrapText = true; }
// cell.SetCellValue(strValue);
// cell.CellStyle = icsHeader;
// //自動設定欄位寬度
// sheet.AutoSizeColumn(j);
// j++;
// }
//}
foreach (GridViewRow gvr in gvExportData.Rows)
{
CheckBoxList cblExportData = (CheckBoxList)gvr.FindControl("chExportData");
int intMID = Convert.ToInt32(gvExportData.DataKeys[gvr.RowIndex].Value);
if (cblExportData != null)
{
if (intMID != 16)//建立非第16個模組的Excel表頭
{
foreach (ListItem liExportData in cblExportData.Items)
{
if (liExportData.Selected && liExportData.Value != "0")
{
//記錄勾選要匯出的欄位
strExportData += "；" + liExportData.Value;
ICell cell;
if (j == 0)
{
cell = row.CreateCell(j);
strValue = "No.";
cell.SetCellValue(strValue);
cell.CellStyle = icsHeader;
//自動設定欄位寬度
sheet.AutoSizeColumn(j);
j++;
}
cell = row.CreateCell(j);
strValue = liExportData.Value.Split('／')[2].ToString().Replace("_", " ").Replace("$", "/").Replace("@", ".").Replace("#td#", "-").Replace("#thl#", "(").Replace("#thr#", ")").Replace("#a#", "&");
row.HeightInPoints = 24;
if (strValue.Contains("#br#"))
{
icsHeader.WrapText = true;
strValue = strValue.Replace("#br#", "\n");
//因為換行所以將Row的高度變成4倍
//row.HeightInPoints = 2 * sheet.DefaultRowHeight / 20;
//row.HeightInPoints = 24;
}
else { icsHeader.WrapText = true; }
cell.SetCellValue(strValue);
cell.CellStyle = icsHeader;
//自動設定欄位寬度
sheet.AutoSizeColumn(j);
j++;
}
}
}
}
}
//建立第16個模組表頭
DataTable dtFreqTitle;
DataTable dtFreq;
int intTitleIndex = j;
string[] strTitle3 = { };
if (blnFrequency)
{
cmd = new SqlCommand("STP_ImaFrequencyTitle");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@wowi_tech_id", liTechnology.Value);
dtFreqTitle = SQLUtil.QueryDS(cmd).Tables[0];
if (dtFreqTitle.Rows.Count > 0)
{
//int intTitleIndex = j;
int intTitle2Index = 0;
IRow row1 = sheet.CreateRow(1);
IRow row2 = sheet.CreateRow(2);
ICell cell;
foreach (DataRow dr in dtFreqTitle.Rows)
{
strTitle3 = dr["Title3"].ToString().Split(';');
for (int i = 0; i <= strTitle3.Length - 1; i++)
{
//第0列
cell = row.CreateCell(intTitleIndex + i);
cell.SetCellValue(dr["TechnologyCategoryName"].ToString());
cell.CellStyle = icsHeader;
cell = row1.CreateCell(intTitleIndex + i);
cell.SetCellValue(dr["Frequency"].ToString().Replace("#br#", "\n"));
cell.CellStyle = icsHeader;
if (dr["Frequency"].ToString().Contains("#br#"))
{
//因為換行所以將Row的高度變成3倍
row1.HeightInPoints = 3 * sheet.DefaultRowHeight / 20;
}
else
{
row1.HeightInPoints = 24;
}
cell = row2.CreateCell(intTitleIndex + i);
cell.SetCellValue(strTitle3.GetValue(i).ToString());
cell.CellStyle = icsHeader;
row2.HeightInPoints = 24;
//自動設定欄位寬度
sheet.AutoSizeColumn(intTitleIndex + i);
}
intTitleIndex += strTitle3.Length;
//合併第二列的欄位
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, j + intTitle2Index * strTitle3.Length, intTitleIndex - 1));
intTitle2Index++;
//建立備註欄位
if (intTitle2Index == dtFreqTitle.Rows.Count && Convert.ToBoolean(dr["IsRemark"]))
{
cell = row.CreateCell(intTitleIndex);
cell.CellStyle = icsHeader;
cell = row1.CreateCell(intTitleIndex);
cell.SetCellValue("Remark");
cell.CellStyle = icsHeader;
cell = row2.CreateCell(intTitleIndex);
cell.CellStyle = icsHeader;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, intTitleIndex, intTitleIndex));
//自動設定欄位寬度
sheet.AutoSizeColumn(intTitleIndex);
//再加1是因為合併第一列欄位時減1的原因
intTitleIndex += 1;
}
}
//合併第一列欄位
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, j, intTitleIndex - 1));
//建立第2及第3列前n欄表頭，並且上下欄位合併
for (int i = 1; i <= 2; i++)
{
row = sheet.GetRow(i);
for (int k = 0; k < j; k++)
{
cell = row.CreateCell(k);
cell.CellStyle = icsHeader;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, k, k));
}
}
}
else//Technology沒有對應的Frequency
{
blnFrequency = false;
}
}
//若沒有勾選模組16則資料從第1列開始寫入，若有則從第3列開始寫入
int intRowIndex = 1;
if (blnFrequency)
{
intRowIndex = 3;
}
//將要匯出的欄位寫入一維陣列
if (strExportData.Length > 0) { strExportData = strExportData.Remove(0, 1); }
string[] strColumn = strExportData.Split('；');
DataView dv;
string strCountryID;
string strRegionID;
//建立資料intTitleIndex
for (int i = 0; i <= dsTech.Tables[0].Rows.Count - 1; i++)
{
strCountryID = dsTech.Tables[0].Rows[i]["country_id"].ToString();
strRegionID = dsTech.Tables[0].Rows[i]["world_region_id"].ToString();
row = sheet.CreateRow(intRowIndex);
for (int k = 0; k <= strColumn.Length; k++)
{
ICell cell;
if (k == 0)
{
cell = row.CreateCell(k);
cell.SetCellValue(i + 1);
}
else
{
cell = row.CreateCell(k);
strValue = strColumn[k - 1].ToString();
DataTable dt;
string str = "";
switch (strValue.Split('／')[1].ToString())
{
case "0":
strValue = dsTech.Tables[Convert.ToInt32(strValue.Split('／')[1])].Rows[i][strValue.Split('／')[2].ToString()].ToString();
break;
//case "1":2012/10/05取消與Technology的關連
//case "2":2012/10/05取消與Technology的關連
//case "3":2012/10/05取消與Technology的關連
case "8":
//CountryID及TechnologyID篩選
dv = dsTech.Tables[Convert.ToInt32(strValue.Split('／')[1])].DefaultView;
//dv.RowFilter = "country_id=" + strCountryID + " and wowi_tech_id=" + liTechnology.Value;
dv.RowFilter = "country_id=" + strCountryID;
//判斷是否為Fee schedule 加入 wowi_tech_id is null
if (strValue.Split('／')[1].ToString().Trim() == "8") { dv.RowFilter += " and (wowi_tech_id is null or wowi_tech_id=" + liTechnology.Value + ")"; }
else { dv.RowFilter += " and wowi_tech_id=" + liTechnology.Value; }
dt = dv.ToTable();
foreach (DataRow dr in dt.Rows)
{
str += "\n" + dr[strValue.Split('／')[2].ToString()].ToString();
}
if (str.Length > 0) { strValue = str.Remove(0, 1); }
else { strValue = ""; }
break;
case "1"://2012/10/05取消與Technology的關連
case "2"://2012/10/05取消與Technology的關連
case "3"://2012/10/05取消與Technology的關連
case "4":
case "5":
case "6":
case "7":
case "9":
case "10":
case "11":
//CountryID篩選
dv = dsTech.Tables[Convert.ToInt32(strValue.Split('／')[1])].DefaultView;
dv.RowFilter = "country_id=" + strCountryID;
dt = dv.ToTable();
foreach (DataRow dr in dt.Rows)
{
str += "\n" + dr[strValue.Split('／')[2].ToString()].ToString();
}
if (str.Length > 0) { strValue = str.Remove(0, 1); }
else { strValue = ""; }
break;
}
//判斷是否有換行符號
if (strValue.Contains("#br#"))
{
if (strValue.Substring(0, 4) == "#br#")
{
strValue = strValue.Remove(0, 4);
}
}
cell.SetCellValue(strValue.Replace("#br#", "\n"));
}
cell.CellStyle = icsTxt;
}
//第16個模組資料
if (blnFrequency)
{
cmd = new SqlCommand("STP_ImaFrequencyGet");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@world_region_id", strRegionID);
cmd.Parameters.AddWithValue("@country_id", strCountryID);
cmd.Parameters.AddWithValue("@wowi_tech_id", liTechnology.Value);
dtFreq = SQLUtil.QueryDS(cmd).Tables[0];
ICell cell;
if (dtFreq.Rows.Count > 0)
{
int intTechnologyCategoryID = Convert.ToInt32(dtFreq.Rows[0]["TechnologyCategoryID"]);
int intShowColumn = 0;
int intCalcRow = 0;//記錄dtFreq跑迴圈到第幾筆資料
string[] strFreColumn = { "IsAllowedN", "PowerLimit", "DoorAllowed", "HT", "TPCDFS" };
//strTitle3 = dtFreq.Rows[0]["Title3"].ToString().Split(';');
intShowColumn = strTitle3.Length;
foreach (DataRow dr in dtFreq.Rows)
{
for (int l = strColumn.Length + 1 + intShowColumn * intCalcRow; l <= strColumn.Length + intShowColumn + intShowColumn * intCalcRow; l++)
{
string strFreqValue = "";
for (int intK = 0; intK <= intShowColumn - 1; intK++)
{
cell = row.CreateCell(l);
strFreqValue = dr[strFreColumn.GetValue(intK).ToString()].ToString().Trim();
if (strFreqValue.Contains("#br#"))
{
if (strFreqValue.Substring(0, 4) == "#br#")
{
strFreqValue = strFreqValue.Remove(0, 4);
strFreqValue = strFreqValue.Replace("#br#", "\n");
}
else { strFreqValue = strFreqValue.Replace("#br#", "\n"); }
}
cell.SetCellValue(strFreqValue);
cell.CellStyle = icsTxt;
l++;
}
}
intCalcRow++;
}
//判斷是否有Remark備註資料
if (Convert.ToBoolean(dtFreq.Rows[0]["IsRemark"]))
{
cell = row.CreateCell(strColumn.Length + intShowColumn * intCalcRow + 1);
cell.SetCellValue(dtFreq.Rows[0]["Remark"].ToString());
cell.CellStyle = icsTxt;
}
}
else
{
for (int l = strColumn.Length + 1; l <= intTitleIndex - 1; l++)
{
cell = row.CreateCell(l);
cell.CellStyle = icsTxt;
}
}
}
intRowIndex++;
}
}
}
//Add Export Flow by Adams 2012/11/12======================================================
string ExportPathNoEncrypt =
System.Configuration.ConfigurationManager.AppSettings["IMAExportPathNoEncrypt"].ToString();
string ExportPathWithEncrypt =
System.Configuration.ConfigurationManager.AppSettings["IMAExportPathWithEncrypt"].ToString();
FileStream fs = new FileStream(ExportPathNoEncrypt + strFile, FileMode.Create);
workbook.Write(fs);
//workbook.Write(ms);
//Response.BinaryWrite(ms.ToArray());
sheet = null;
workbook.Dispose();
workbook = null;
ms.Flush();
ms.Position = 0;
ms.Close();
ms.Dispose();
//base.Response.End();
fs.Close();
//產生加密的Excel檔
string openPassword = ExcelEncrypt.GenerateRandomCode();
string writePassword = ExcelEncrypt.GenerateRandomCode();
ExcelEncrypt.EncryptExcelByPassword(ExportPathNoEncrypt + strFile,
ExportPathWithEncrypt + strFile,
openPassword,
openPassword);
//傳送MAIL
string mailfrom = "*****@*****.**";
string mailTo = System.Configuration.ConfigurationManager.AppSettings["IMAExportApprovor"].ToString();
string mailSubject = "[IMA Notice]" + User.Identity.Name + " had Export IMA Document at " + DateTime.Now.ToString();
string mailBody =
"<br/> Dear Approver:" +
"<br/> The password of IMA Export Excel File：" + openPassword +
"<br/> Please refer to the attachment file";
MailUtil.SendMailWithAttachment(mailfrom, mailTo, mailSubject, mailBody, ExportPathWithEncrypt + strFile);
//Add Export Flow by Adams 2012/11/12======================================================
Message.Text = "檔案 : " + strFile + " 匯出完成，請待主管審核確認!";
}
/// <summary>
/// 将DataSet数据集转换HSSFworkbook对象，并保存为Stream流
/// </summary>
/// <param name="ds"></param>
/// <returns>返回数据流Stream对象</returns>
public static MemoryStream ExportDatasetToExcel(DataSet ds, string fileName, string months)
{
#region
//Hashtable ht = new Hashtable();
//ht.Add("orderNum", "订单号码");
//ht.Add("createTime", "创建日期");
//ht.Add("cityId", "订单城市");
//ht.Add("hotelName", "酒店名称");
//ht.Add("guestName", "入住人名称");
//ht.Add("inDate", "入住日期");
//ht.Add("outDate", "离店日期");
//ht.Add("bookTotalPrice", "房间单价");
//ht.Add("rooms", "间数");
//ht.Add("bookPrice", "总价");
//ht.Add("bookStatusOther", "订单状态");
//ht.Add("commission", "佣金");
//ht.Add("orderChannel", "渠道");
#endregion
try
{
//文件流对象
MemoryStream stream = new MemoryStream();
//打开Excel对象
HSSFWorkbook workbook = new HSSFWorkbook();
//Excel的Sheet对象
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 12 * 256);
sheet.SetColumnWidth(3, 12 * 256);
sheet.SetColumnWidth(4, 12 * 256);
sheet.SetColumnWidth(5, 5 * 256);
sheet.SetColumnWidth(6, 9 * 256);
sheet.SetColumnWidth(7, 9 * 256);
sheet.SetColumnWidth(8, 9 * 256);
sheet.SetColumnWidth(9, 8 * 256);
sheet.SetMargin(MarginType.RightMargin, (double)0.1);
sheet.SetMargin(MarginType.TopMargin, (double)0.1);
sheet.SetMargin(MarginType.LeftMargin, (double)0.1);
sheet.SetMargin(MarginType.BottomMargin, (double)0.1);
sheet.PrintSetup.PaperSize = 9;
sheet.PrintSetup.FitWidth = 800;
sheet.PrintSetup.FitHeight = 1000;
//set date format
//CellStyle cellStyleDate = workbook.CreateCellStyle();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");
//使用NPOI操作Excel表
//合并消息头
NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cellHeader = rowHeader.CreateCell(0);
cellHeader.SetCellValue("今夜酒店特价 HotelVP.com 佣金结算对账单");
cellStyleDate.Alignment = HorizontalAlignment.CENTER;
cellStyleDate.VerticalAlignment = VerticalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeight = 20 * 20;
cellStyleDate.SetFont(font);
cellHeader.CellStyle = cellStyleDate;
sheet.AddMergedRegion(new CellRangeAddress(0, 3, 0, 9));
//添加Excel头信息内容
//分销渠道：去哪儿
NPOI.SS.UserModel.ICell cellKey;
NPOI.SS.UserModel.ICell cellValue;
IFont font12 = workbook.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "微软雅黑";
//font12.FontHeight = 20 * 20;
NPOI.SS.UserModel.IRow channelRow = sheet.CreateRow(7);
channelRow.Height = 20 * 20;
cellKey = channelRow.CreateCell(0);
cellKey.CellStyle.SetFont(font12);
//cellKey.CellStyle.FillPattern = FillPatternType.
cellKey.SetCellValue("To:");
cellValue = channelRow.CreateCell(1);
cellValue.CellStyle.SetFont(font12);
//cellValue.SetCellValue("去哪儿");
cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店名称"].ToString());
cellKey = channelRow.CreateCell(4);
cellKey.SetCellValue("From:");
cellValue = channelRow.CreateCell(5);
//cellValue.SetCellValue("去哪儿");
cellValue.SetCellValue("结算部");
//报表日期段：xxxx
NPOI.SS.UserModel.IRow reportDateRow = sheet.CreateRow(8);
cellKey = reportDateRow.CreateCell(0);
cellKey.SetCellValue("Tel:");
cellValue = reportDateRow.CreateCell(1);
//cellValue.SetCellValue("2012/12/01--2012/12/13");
cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店电话"].ToString());
cellKey = reportDateRow.CreateCell(4);
cellKey.SetCellValue("Tel:");
cellValue = reportDateRow.CreateCell(5);
//cellValue.SetCellValue("2012/12/01--2012/12/13");
cellValue.SetCellValue("021-52186026、52186036");
//报表生成日期：yyyy-mm-dd hh-mm-ss
NPOI.SS.UserModel.IRow reportCreateDateRow = sheet.CreateRow(9);
cellKey = reportCreateDateRow.CreateCell(0);
cellKey.SetCellValue("Fax:");
cellValue = reportCreateDateRow.CreateCell(1);
cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店传真"].ToString());
cellKey = reportCreateDateRow.CreateCell(4);
cellKey.SetCellValue("Fax:");
cellValue = reportCreateDateRow.CreateCell(5);
cellValue.SetCellValue("021-52186011");
//结算总价
NPOI.SS.UserModel.IRow SettlementPriceRow = sheet.CreateRow(10);
cellKey = SettlementPriceRow.CreateCell(0);
cellKey.SetCellValue("Id:");
cellValue = SettlementPriceRow.CreateCell(1);
//cellValue.SetCellValue("111122233");
cellValue.SetCellValue(ds.Tables[0].Rows[0]["酒店ID"].ToString());
cellKey = SettlementPriceRow.CreateCell(4);
cellKey.SetCellValue("Email:");
cellValue = SettlementPriceRow.CreateCell(5);
//cellValue.SetCellValue("111122233");
cellValue.SetCellValue("*****@*****.**");
//结算总价
NPOI.SS.UserModel.IRow remarkRow = sheet.CreateRow(12);
cellKey = remarkRow.CreateCell(0);
cellKey.SetCellValue("您好！以下为2013年" + months + "月佣金结算对账单，烦请贵酒店相关负责人员核对以下数据");
NPOI.SS.UserModel.IRow remark1Row = sheet.CreateRow(13);
cellKey = remark1Row.CreateCell(0);
cellKey.SetCellValue("若无误请于收到对账单二日内签字盖章确认回传，若有疑问，请及时告知，非常感谢！");
//结算总价
NPOI.SS.UserModel.IRow totalPrice1Row = sheet.CreateRow(16);
cellKey = totalPrice1Row.CreateCell(0);
cellKey.SetCellValue("总间夜数:");
NPOI.SS.UserModel.ICell cellValue1;
cellValue1 = totalPrice1Row.CreateCell(1);
//cellValue1.CellStyle.Alignment = HorizontalAlignment.CENTER;
cellValue1.SetCellValue(ds.Tables[0].Compute("SUM(间夜)", "").ToString());
cellValue = totalPrice1Row.CreateCell(2);
cellValue.SetCellValue("间夜");
cellValue = totalPrice1Row.CreateCell(4);
cellValue.SetCellValue("签字确认:");
cellValue = totalPrice1Row.CreateCell(5);
cellValue.SetCellValue("");
NPOI.SS.UserModel.IRow totalPrice2Row = sheet.CreateRow(17);
cellKey = totalPrice2Row.CreateCell(0);
cellKey.SetCellValue("佣金合计:");
NPOI.SS.UserModel.ICell cellValue2;
cellValue2 = totalPrice2Row.CreateCell(1);
//cellValue2.CellStyle.Alignment = HorizontalAlignment.CENTER;
cellValue2.SetCellValue(ds.Tables[0].Compute("SUM(佣金)", "").ToString());
cellValue = totalPrice2Row.CreateCell(2);
cellValue.SetCellValue("人民币（元）");
cellValue = totalPrice2Row.CreateCell(4);
cellValue.SetCellValue("发票抬头:");
cellValue = totalPrice2Row.CreateCell(5);
cellValue.SetCellValue("");
NPOI.SS.UserModel.IRow totalPrice3Row = sheet.CreateRow(18);
cellKey = totalPrice3Row.CreateCell(0);
cellKey.SetCellValue("前期未付佣金:");
cellValue = totalPrice3Row.CreateCell(1);
cellValue.SetCellValue("");
cellValue = totalPrice3Row.CreateCell(2);
cellValue.SetCellValue("人民币（元）");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(21);
int count = 0;
for (int i = 4; i < ds.Tables[0].Columns.Count; i++) //生成sheet第一行列名
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(count++);
cell.SetCellValue(ds.Tables[0].Columns[i].ColumnName.ToString());
}
//将数据导入到excel表中
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rows = sheet.CreateRow(i + 22);
count = 0;
for (int j = 4; j < ds.Tables[0].Columns.Count; j++)
{
if (ds.Tables[0].Columns[j].ColumnName != null)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(count++);
Type type = ds.Tables[0].Rows[i][j].GetType();
if (type == typeof(int) || type == typeof(Int16)
|| type == typeof(Int32) || type == typeof(Int64))
{
cell.SetCellValue((int)ds.Tables[0].Rows[i][j]);
}
else
{
if (type == typeof(float) || type == typeof(double) || type == typeof(Double))
{
cell.SetCellValue((Double)ds.Tables[0].Rows[i][j]);
}
else
{
if (type == typeof(DateTime))
{
cell.SetCellValue(((DateTime)ds.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd HH:mm"));
}
else
{
if (type == typeof(bool) || type == typeof(Boolean))
{
cell.SetCellValue((bool)ds.Tables[0].Rows[i][j]);
}
else
{
cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString());
}
}
}
}
}
}
}
NPOI.SS.UserModel.IRow totalInfo1Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 1);
cellValue = totalInfo1Row.CreateCell(0);
cellValue.SetCellValue("Total：");
cellValue = totalInfo1Row.CreateCell(5);
cellValue.SetCellValue(ds.Tables[0].Compute("SUM(间夜)", "").ToString());
cellValue = totalInfo1Row.CreateCell(6);
cellValue.SetCellValue(ds.Tables[0].Compute("SUM(卖价)", "").ToString());
cellValue = totalInfo1Row.CreateCell(7);
cellValue.SetCellValue(ds.Tables[0].Compute("SUM(底价)", "").ToString());
cellValue = totalInfo1Row.CreateCell(8);
cellValue.SetCellValue(ds.Tables[0].Compute("SUM(佣金)", "").ToString());
//结算总价
NPOI.SS.UserModel.IRow companyinfoRow = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 5);
cellKey = companyinfoRow.CreateCell(0);
cellKey.SetCellValue("我司汇款资料");
NPOI.SS.UserModel.IRow companyinfo1Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 6);
cellValue = companyinfo1Row.CreateCell(0);
cellValue.SetCellValue("银行账号：");
cellValue = companyinfo1Row.CreateCell(1);
cellValue.SetCellValue("31001515100050024076");
NPOI.SS.UserModel.IRow companyinfo2Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 7);
cellValue = companyinfo2Row.CreateCell(0);
cellValue.SetCellValue("开户银行：");
cellValue = companyinfo2Row.CreateCell(1);
cellValue.SetCellValue("中国建设银行股份有限公司上海长宁支行");
NPOI.SS.UserModel.IRow companyinfo3Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 8);
cellValue = companyinfo3Row.CreateCell(0);
cellValue.SetCellValue("公司全称：");
cellValue = companyinfo3Row.CreateCell(1);
cellValue.SetCellValue("上海千栈网络信息科技有限公司");
//保存excel文档
sheet.ForceFormulaRecalculation = true;
using (Stream filstream = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(filstream);
}
workbook.Dispose();
return stream;
}
catch (Exception ex)
{
return new MemoryStream();
}
}
public static MemoryStream ExportToExcel(List<Group> list, List<string> date)
{
try
{
//文件流对像
MemoryStream stream = new MemoryStream();
//打开Excel对象
HSSFWorkbook workbook = new HSSFWorkbook();
Font aFont = workbook.CreateFont();
aFont.FontName = "Calibri";
aFont.FontHeightInPoints = 14;
//aFont.Boldweight = short.MaxValue;
CellStyle projectStyle = workbook.CreateCellStyle();
//对齐方式
projectStyle.Alignment = HorizontalAlignment.CENTER;
projectStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
////边框
projectStyle.BorderTop = CellBorderType.MEDIUM;
projectStyle.BorderRight = CellBorderType.MEDIUM;
projectStyle.BorderBottom = CellBorderType.THIN;
projectStyle.BorderLeft = CellBorderType.MEDIUM;
//字体
projectStyle.SetFont(aFont);
//背景颜色
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex((short)9, (byte)(240), (byte)(240), (byte)(240));
palette.SetColorAtIndex((short)10, (byte)(220), (byte)(220), (byte)(220));
projectStyle.FillForegroundColor=(short)9;
projectStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//projectStyle.FillBackgroundColor = (short)9;
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
headStyle.BorderTop = CellBorderType.MEDIUM;
headStyle.BorderRight = CellBorderType.THIN;
headStyle.SetFont(aFont);
//headStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
//headStyle.FillPattern = FillPatternType.SQUARES;
//headStyle.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
headStyle.FillForegroundColor = (short)9;
headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
CellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.Alignment = HorizontalAlignment.CENTER;
dateStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
dateStyle.BorderTop = CellBorderType.MEDIUM;
dateStyle.BorderRight = CellBorderType.MEDIUM;
dateStyle.BorderLeft = CellBorderType.MEDIUM;
Font bFont = workbook.CreateFont();
bFont.FontName = "Calibri";
bFont.FontHeightInPoints = 12;
dateStyle.SetFont(bFont);
CellStyle projectstyle = workbook.CreateCellStyle();
//对齐方式
projectstyle.Alignment = HorizontalAlignment.CENTER;
projectstyle.VerticalAlignment = VerticalAlignment.CENTER;
////边框
projectstyle.BorderRight = CellBorderType.MEDIUM;
projectstyle.BorderBottom = CellBorderType.MEDIUM;
//projectstyle.BorderLeft = CellBorderType.MEDIUM;
projectstyle.SetFont(bFont);
CellStyle nameStyle = workbook.CreateCellStyle();
nameStyle.SetFont(bFont);
//对齐方式
nameStyle.Alignment = HorizontalAlignment.CENTER;
nameStyle.VerticalAlignment = VerticalAlignment.CENTER;
////边框
nameStyle.BorderTop = CellBorderType.THIN;
nameStyle.BorderRight = CellBorderType.THIN;
nameStyle.BorderBottom = CellBorderType.THIN;
nameStyle.BorderLeft = CellBorderType.MEDIUM;
CellStyle itemStyle = workbook.CreateCellStyle();
nameStyle.SetFont(bFont);
//对齐方式
//itemStyle.Alignment = HorizontalAlignment.CENTER;
itemStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
////边框
itemStyle.BorderTop = CellBorderType.THIN;
itemStyle.BorderRight = CellBorderType.THIN;
itemStyle.BorderBottom = CellBorderType.THIN;
itemStyle.BorderLeft = CellBorderType.THIN;
CellStyle datestyle = workbook.CreateCellStyle();
datestyle.SetFont(bFont);
//对齐方式
datestyle.Alignment = HorizontalAlignment.RIGHT;
datestyle.VerticalAlignment = VerticalAlignment.BOTTOM;
////边框
datestyle.BorderTop = CellBorderType.THIN;
datestyle.BorderRight = CellBorderType.MEDIUM;
datestyle.BorderBottom = CellBorderType.THIN;
datestyle.BorderLeft = CellBorderType.MEDIUM;
CellStyle emptyStyle = workbook.CreateCellStyle();
//对齐方式
emptyStyle.Alignment = HorizontalAlignment.CENTER;
emptyStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
//背景颜色
emptyStyle.FillForegroundColor = (short)10;
emptyStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
////边框
emptyStyle.BorderTop = CellBorderType.THIN;
emptyStyle.BorderRight = CellBorderType.MEDIUM;
emptyStyle.BorderBottom = CellBorderType.THIN;
emptyStyle.BorderLeft = CellBorderType.MEDIUM;
//emptyStyle.FillBackgroundColor = (short)8;
CellStyle lastLineStyle = workbook.CreateCellStyle();
lastLineStyle.BorderTop = CellBorderType.MEDIUM;
//Excel的Sheet对象
Sheet sheet = workbook.CreateSheet("sheet1");
sheet.SetColumnWidth(0, 22 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 24 * 256);
sheet.DefaultRowHeightInPoints = 20;
sheet.CreateFreezePane(3, 0);//Column Locking
//sheet.CreateFreezePane(3, 0, 0, 0);
////set date format
//CellStyle cellStyleDate = workbook.CreateCellStyle();
//DataFormat format = workbook.CreateDataFormat();
//cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");
//将数据导入到excel表中
Row row;
Cell cell;
CellRangeAddress range;
int count = 1;
foreach (var project in list)
{
int column = 0;
row = sheet.CreateRow(count);
cell = row.CreateCell(0);
cell.SetCellValue("Project");
cell.CellStyle = projectStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Name");
cell.CellStyle = headStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Check Item");
cell.CellStyle = headStyle;
foreach (var dateitem in date)
{
sheet.SetColumnWidth(column + 3, 12 * 256);
cell = row.CreateCell(column + 3);
cell.SetCellValue(dateitem);
cell.CellStyle = dateStyle;
column++;
}
count++;
int startCount = count;
foreach (var user in project.Monthly)
{
Row row1 = sheet.CreateRow(count);
Row row2 = sheet.CreateRow(count + 1);
Row row3 = sheet.CreateRow(count + 2);
Row row4 = sheet.CreateRow(count + 3);
cell = row1.CreateCell(0);
cell.SetCellValue(project.GroupName);
cell.CellStyle = projectstyle;
//cell = row2.CreateCell(0);
//cell.CellStyle = projectstyle;
//cell = row3.CreateCell(0);
//cell.CellStyle = projectstyle;
//cell = row4.CreateCell(0);
//cell.CellStyle = projectstyle;
cell = row1.CreateCell(1);
cell.SetCellValue(user.UserName);
cell.CellStyle = nameStyle;
cell = row2.CreateCell(1);
cell.CellStyle = nameStyle;
cell = row3.CreateCell(1);
cell.CellStyle = nameStyle;
cell = row4.CreateCell(1);
cell.CellStyle = nameStyle;
cell = row1.CreateCell(2);
cell.SetCellValue("Work Done");
cell.CellStyle = itemStyle;
cell = row2.CreateCell(2);
cell.SetCellValue("Bug Created");
cell.CellStyle = itemStyle;
cell = row3.CreateCell(2);
cell.SetCellValue("Work Quality");
cell.CellStyle = itemStyle;
cell = row4.CreateCell(2);
cell.SetCellValue("Daily Performance");
cell.CellStyle = itemStyle;
range = new CellRangeAddress(count, count + 3, 1, 1);
sheet.AddMergedRegion(range);
int num = 0;
foreach (var item in user.MonthlyAppraisal)
{
cell = row1.CreateCell(num + 3);
if (item.WorkDone != 0)
{
cell.SetCellValue((Double)item.WorkDone);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
cell = row2.CreateCell(num + 3);
if (item.BugCreated != 0)
{
cell.SetCellValue((Double)item.BugCreated);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
cell = row3.CreateCell(num + 3);
if (item.WorkQuality != 0)
{
cell.SetCellValue((Double)item.WorkQuality);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
cell = row4.CreateCell(num + 3);
if (item.DailyPerformance != 0)
{
cell.SetCellValue((Double)item.DailyPerformance);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
num++;
}
count += 4;
}
range = new CellRangeAddress(startCount, count - 1, 0, 0);
sheet.AddMergedRegion(range);
}
row = sheet.CreateRow(count);
int m = date.Count;
for (int i = 0; i < m + 3; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = lastLineStyle;
}
//保存excel文档
sheet.ForceFormulaRecalculation = true;
workbook.Write(stream);
workbook.Dispose();
return stream;
}
catch
{
return new MemoryStream();
}
}
public static MemoryStream ExportDatasetToExcel(List<Tuple<string, List<Tuple<string, string>>>> list)
{
try
{
//File stream object
MemoryStream stream = new MemoryStream();
//Open Excel object
HSSFWorkbook workbook = new HSSFWorkbook();
//Head Style
CellStyle headstyle = workbook.CreateCellStyle();
//Alignment
headstyle.Alignment = HorizontalAlignment.CENTER;
headstyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font headfont = workbook.CreateFont();
headfont.FontHeightInPoints = 12;
headfont.Boldweight = short.MaxValue;
headstyle.SetFont(headfont);
//Background color
headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headstyle.FillPattern = FillPatternType.SQUARES;
headstyle.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
//Border
headstyle.BorderTop = CellBorderType.THIN;
headstyle.BorderRight = CellBorderType.THIN;
headstyle.BorderBottom = CellBorderType.THIN;
headstyle.BorderLeft = CellBorderType.THIN;
//Body Style
CellStyle bodystyle = workbook.CreateCellStyle();
//Border
bodystyle.BorderTop = CellBorderType.THIN;
bodystyle.BorderRight = CellBorderType.THIN;
bodystyle.BorderBottom = CellBorderType.THIN;
bodystyle.BorderLeft = CellBorderType.THIN;
//Line Feed
bodystyle.WrapText = true;
//Sheet's object of Excel
Sheet sheet = workbook.CreateSheet("sheet1");
sheet.SetColumnWidth(0, 5000);
sheet.SetColumnWidth(1, 100000);
////set date format
//CellStyle cellStyleDate = workbook.CreateCellStyle();
//DataFormat format = workbook.CreateDataFormat();
//cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");
//Export to Excel
Row row;
Cell cell;
//cell.SetCellType();
int count = 0;
foreach (var l in list)
{
if (l.Item2.Count != 0)
{
row = sheet.CreateRow(count);
row.HeightInPoints = 20;
cell = row.CreateCell(0);
cell.CellStyle = headstyle;
cell.SetCellValue(l.Item1);
cell = row.CreateCell(1);
cell.CellStyle = headstyle;
CellRangeAddress range = new CellRangeAddress(count, count, 0, 1);
sheet.AddMergedRegion(range);
count++;
foreach (var item in l.Item2)
{
row = sheet.CreateRow(count);
cell = row.CreateCell(0);
cell.SetCellValue(item.Item1);
cell.CellStyle = bodystyle;
cell = row.CreateCell(1);
cell.SetCellValue(item.Item2);
cell.CellStyle = bodystyle;
count++;
}
}
}
//Save excel
sheet.ForceFormulaRecalculation = true;
workbook.Write(stream);
workbook.Dispose();
return stream;
}
catch
{
return new MemoryStream();
}
}
public static MemoryStream ExportDataToExcel(List<Tuple<string, string, string>> list)
{
try
{
//File stream object
MemoryStream stream = new MemoryStream();
//Open Excel object
HSSFWorkbook workbook = new HSSFWorkbook();
//custom color
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex((short)15, (byte)(184), (byte)(134), (byte)(11));
palette.SetColorAtIndex((short)16, (byte)(255), (byte)(240), (byte)(215));
//Title Style
CellStyle titlestyle = workbook.CreateCellStyle();
//Alignment
titlestyle.Alignment = HorizontalAlignment.CENTER;
titlestyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font titlefont = workbook.CreateFont();
titlefont.FontHeightInPoints = 16;
titlefont.FontName = "Calibri";
titlefont.Color = HSSFColor.WHITE.index;
titlefont.Boldweight = (short)FontBoldWeight.BOLD;
titlestyle.SetFont(titlefont);
//Background color
titlestyle.FillForegroundColor = (short)15;
titlestyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//Border
titlestyle.BorderTop = CellBorderType.THIN;
titlestyle.BorderRight = CellBorderType.THIN;
titlestyle.BorderBottom = CellBorderType.THIN;
titlestyle.BorderLeft = CellBorderType.THIN;
//Head Style
CellStyle headstyle = workbook.CreateCellStyle();
//Alignment
headstyle.Alignment = HorizontalAlignment.CENTER;
headstyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font headfont = workbook.CreateFont();
headfont.FontHeightInPoints = 14;
headfont.FontName = "Calibri";
headfont.Boldweight = (short)FontBoldWeight.BOLD;
headstyle.SetFont(headfont);
//Background color
headstyle.FillForegroundColor = (short)16;
headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//Border
headstyle.BorderTop = CellBorderType.THIN;
headstyle.BorderRight = CellBorderType.THIN;
headstyle.BorderBottom = CellBorderType.THIN;
headstyle.BorderLeft = CellBorderType.THIN;
//Name Style
CellStyle namestyle = workbook.CreateCellStyle();
//Alignment
namestyle.Alignment = HorizontalAlignment.CENTER;
namestyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font namefont = workbook.CreateFont();
namefont.FontHeightInPoints = 11;
namefont.FontName = "Calibri";
namefont.Boldweight = (short)FontBoldWeight.BOLD;
namestyle.SetFont(namefont);
//Border
namestyle.BorderTop = CellBorderType.THIN;
namestyle.BorderRight = CellBorderType.THIN;
namestyle.BorderBottom = CellBorderType.THIN;
namestyle.BorderLeft = CellBorderType.THIN;
//Body Style
CellStyle bodystyle = workbook.CreateCellStyle();
//Alignment
bodystyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font bodyfont = workbook.CreateFont();
bodyfont.FontHeightInPoints = 12;
bodyfont.FontName = "Times New Roman";
bodystyle.SetFont(bodyfont);
//Border
bodystyle.BorderTop = CellBorderType.THIN;
bodystyle.BorderRight = CellBorderType.THIN;
bodystyle.BorderBottom = CellBorderType.THIN;
bodystyle.BorderLeft = CellBorderType.THIN;
//Line Feed
bodystyle.WrapText = true;
//Sheet's object of Excel
Sheet sheet = workbook.CreateSheet("sheet1");
sheet.SetColumnWidth(0, (short)(35.7 * 160));
sheet.SetColumnWidth(1, (short)(35.7 * 400));
sheet.SetColumnWidth(2, (short)(35.7 * 600));
//Export to Excel
Row row;
Cell cell;
//cell.SetCellType();
int count = 2;
row = sheet.CreateRow(0);
row.HeightInPoints = 20;
cell = row.CreateCell(0);
cell.CellStyle = titlestyle;
cell.SetCellValue("Staff Report");
cell = row.CreateCell(1);
cell.CellStyle = titlestyle;
cell = row.CreateCell(2);
cell.CellStyle = titlestyle;
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(range);
row = sheet.CreateRow(1);
row.HeightInPoints = 20;
cell = row.CreateCell(0);
cell.CellStyle = headstyle;
cell = row.CreateCell(1);
cell.CellStyle = headstyle;
cell.SetCellValue(" What you worked on today?");
cell = row.CreateCell(2);
cell.CellStyle = headstyle;
cell.SetCellValue(" What you will be working on tomorrow?");
foreach (var item in list)
{
row = sheet.CreateRow(count);
cell = row.CreateCell(0);
cell.SetCellValue(item.Item1);
cell.CellStyle = namestyle;
cell = row.CreateCell(1);
cell.SetCellValue(item.Item2);
cell.CellStyle = bodystyle;
cell = row.CreateCell(2);
cell.SetCellValue(item.Item3);
cell.CellStyle = bodystyle;
count++;
}
//Save excel
sheet.ForceFormulaRecalculation = true;
workbook.Write(stream);
workbook.Dispose();
return stream;
}
catch
{
return new MemoryStream();
}
}