public uint AddStyle(ExcelCellStyle style)
{
var fillId = fillStyles.AddStyle(style.FillStyle);
var fontId = fontStyles.AddFont(style.FontStyle);
var borderId = bordersStyles.AddStyle(style.BordersStyle);
var numberFormatId = numberingFormats.AddFormat(style.NumberingFormat);
var alignment = Alignment(style.Alignment);
var cacheItem = new CellStyleCacheItem
{
FillId = fillId,
FontId = fontId,
BorderId = borderId,
NumberFormatId = numberFormatId,
Alignment = alignment
};
if (!cache.TryGetValue(cacheItem, out var result))
{
result = stylesheet.CellFormats.Count;
stylesheet.CellFormats.Count++;
stylesheet.CellFormats.AppendChild(cacheItem.ToCellFormat());
cache.Add(cacheItem, result);
}
return(result);
}
protected void SetCellType(ref ICell cell, ExcelCellNumericTypeEnum enumExcelNumericType, ExcelCellAreaTypeEnum enumExcelCellAreaType)
{
ExcelCellStyle _ecsTarget = this.m_ecsList.Find(p => p.ExcelCellNumericType == enumExcelNumericType && p.ExcelCellAreaType == enumExcelCellAreaType);
if (_ecsTarget == null)
{
ICellStyle _cellstyle = this.m_xssfWorkbook.CreateCellStyle();
switch (enumExcelNumericType)
{
case ExcelCellNumericTypeEnum.NonNumeric:
break;
case ExcelCellNumericTypeEnum.Integer:
_cellstyle.DataFormat = this.m_xssfWorkbook.CreateDataFormat().GetFormat("#,##0");
break;
case ExcelCellNumericTypeEnum.Percentage:
_cellstyle.DataFormat = this.m_xssfWorkbook.CreateDataFormat().GetFormat("0%");
break;
case ExcelCellNumericTypeEnum.PercentageOnSecondDecimalPoint:
_cellstyle.DataFormat = this.m_xssfWorkbook.CreateDataFormat().GetFormat("0.00%");
break;
}
IFont _font = this.m_xssfWorkbook.CreateFont();
switch (enumExcelCellAreaType)
{
case ExcelCellAreaTypeEnum.Normal:
break;
case ExcelCellAreaTypeEnum.Title:
_cellstyle.Alignment = HorizontalAlignment.Center;
_font.Boldweight = (short)FontBoldWeight.Bold;
_cellstyle.SetFont(_font);
break;
case ExcelCellAreaTypeEnum.SubSummary:
_font.Boldweight = (short)FontBoldWeight.Bold;
_cellstyle.SetFont(_font);
_cellstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
_cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
break;
case ExcelCellAreaTypeEnum.Summary:
_font.Boldweight = (short)FontBoldWeight.Bold;
_cellstyle.SetFont(_font);
_cellstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
_cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
break;
}
_ecsTarget = new ExcelCellStyle(enumExcelNumericType, enumExcelCellAreaType, _cellstyle);
this.m_ecsList.Add(_ecsTarget);
}
cell.CellStyle = _ecsTarget.Style;
}
private void AddAlignmentToCellFormat(ExcelCellStyle item, CellFormat cellFormat)
{
var alignment = new Alignment()
{
WrapText = false, TextRotation = 99, ShrinkToFit = true,
};
if (item.HorizontalAlignment.HasValue)
{
alignment.Horizontal = (HorizontalAlignmentValues)(int)item.HorizontalAlignment.Value;
}
if (item.VerticalAlignment.HasValue)
{
alignment.Vertical = (VerticalAlignmentValues)(int)item.VerticalAlignment.Value;
}
if (item.WrapText.HasValue)
{
alignment.WrapText = item.WrapText.Value;
}
if (item.TextRotation.HasValue)
{
alignment.TextRotation = (uint)item.TextRotation.Value;
}
if (item.ShrinkToFit.HasValue)
{
alignment.ShrinkToFit = item.ShrinkToFit.Value;
}
cellFormat.ApplyAlignment = true;
}
public void Commit()
{
foreach (var rng in _cellRange)
{
if (rng.IsValid)
{
// get cell style
var row = _flex.Rows[rng.Row] as ExcelRow;
var col = _flex.Columns[rng.Column];
ExcelCellStyle excelCellStyle = new ExcelCellStyle();
if (row != null)
{
var cs = row.GetCellStyle(col) as ExcelCellStyle;
if (cs != null)
{
excelCellStyle = cs;
}
}
excelCellStyle.Format = NoDecimalPlacesList.Contains(SelectedNumberFormat)
? SelectedNumberFormat.Format
: SelectedNumberFormat.Format + ((NumberFormat)SelectedNumberFormat).DecimalPlaces;
row.SetCellStyle(_flex.Columns[rng.Column], excelCellStyle);
_flex.Invalidate(rng);
}
}
}
private void AddNumberFormatToCellFormat(ExcelCellStyle item, CellFormat cellFormat)
{
if (item.CellFormat.HasValue)
{
cellFormat.NumberFormatId = (uint)item.CellFormat.Value;
cellFormat.ApplyNumberFormat = true;
}
}
public IExcelCell SetStyle(ExcelCellStyle style)
{
if (style != null)
{
cell.StyleIndex = documentStyle.AddStyle(style);
}
return(this);
}
public ExcelValueObject(string text, ExcelCellFormat format, ExcelCellStyle style, int mergeAcrossCount,
int mergeDownCount)
{
Text = text;
Format = format;
Style = style;
MergeAcrossCount = mergeAcrossCount;
MergeDownCount = mergeDownCount;
}
public void ExcelCellStyle_OneInstance()
{
var cellStyleA = new ExcelCellStyle {
CellFormat = CellFormatEnum.DecimalTwoDecimals, ShrinkToFit = true, BackgroundColor = new ExcelColor("0FA3D1")
};
var cellStyleB = cellStyleA;
cellStyleA.Equals(cellStyleB).ShouldBeTrue();
}
private int _columnCount; //列数
#endregion
#region 构造函数
/// <summary>
/// 创建一个新列头对象
/// </summary>
private ExcelColumn()
{
_isSetWith = false;
_columnCount = 1;
_columnName = string.Empty;
_dataPropertyName = string.Empty;
_with = 20;
_defaultExcelCellStyle = null; // new ExcelCellStyle();
_columnExcelCellStyle = null; //new ExcelCellStyle();
//_warpText = false;
//_index = 0;
}
private void AddFontToCellFormat(ExcelCellStyle item, CellFormat cellFormat)
{
if (item.Font != null)
{
var font = new Font();
if (!string.IsNullOrWhiteSpace(item.Font.FontName))
{
font.AppendChild(new FontName {
Val = item.Font.FontName
});
}
if (item.Font.Size.HasValue)
{
font.AppendChild(new FontSize {
Val = item.Font.Size.Value
});
}
if (item.Font.Color != null)
{
font.AppendChild(new Color {
Rgb = item.Font.Color.ColorHexCode
});
}
if (item.Font.IsBold)
{
font.AppendChild(new Bold {
Val = true
});
}
if (item.Font.IsItalic)
{
font.AppendChild(new Italic {
Val = true
});
}
if (item.Font.IsUnderline)
{
font.AppendChild(new Underline {
Val = UnderlineValues.Single
});
}
cellFormat.FontId = GetElementIndexOrAdd(this.fonts, font);
cellFormat.ApplyFont = true;
}
}
private void ExcelExport()
{
string sheet1Title = "Sheet1";
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(sheet1Title);
NPOI.SS.UserModel.IRow headerRow4Sheet1 = sheet1.CreateRow(0);
ExcelCellStyle cellStyle = new ExcelCellStyle(book);
ICell cell;//初始化ICell准备设值
#region 设定Sheet1的头行
cell = headerRow4Sheet1.CreateCell(0); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户ID");
cell = headerRow4Sheet1.CreateCell(1); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户名");
cell = headerRow4Sheet1.CreateCell(2); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户地址");
#endregion
/*0行已经设为表头行，内容从第1行开始设值*/
int sheet1RowID = 1;
Customer[] listCustomer = client.GetCustomer("");
foreach (Customer customer in listCustomer)
{
IRow r = sheet1.CreateRow(sheet1RowID);
cell = r.CreateCell(0); cell.SetCellValue(customer.customerID); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(1); cell.SetCellValue(customer.customerName); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(2); cell.SetCellValue(customer.customerAddress); cell.CellStyle = cellStyle.style;
sheet1RowID += 1;//每做一个循环就到下一行设置
}
/*以下是做自适应宽度*/
ChangeStyle(book, sheet1);
/*最后作输出流导出文件*/
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("客户列表信息", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
private static ExcelCell AdaptCell(ExcelRangeBase cell)
{
var adr = new models.ExcelCellAddress(cell.Start.Row, cell.End.Column);
var rgb = cell.Style.Fill.BackgroundColor.Rgb;
var color = Color.Transparent;
if (!string.IsNullOrEmpty(rgb))
{
var argb = int.Parse(rgb, NumberStyles.HexNumber);
color = Color.FromArgb(argb);
}
var style = new ExcelCellStyle(color);
var excelCell = new ExcelCell(cell.Value, adr, style);
return(excelCell);
}
public void Commit()
{
var cell = _cellRange.FirstOrDefault();
var row = _flex.Rows[cell.TopRow] as ExcelRow;
ExcelCellStyle excelCellStyle = new ExcelCellStyle();
if (row != null && cell.IsValid)
{
var col = _flex.Columns[cell.Column];
var cs = row.GetCellStyle(col) as ExcelCellStyle;
if (cs != null)
{
excelCellStyle = cs;
}
}
if (ShowBorderTop)
{
SetBorderBrush(BorderAlign.Top);
excelCellStyle.CellBorderBrushTop = CellBorderBrushTop;
}
if (ShowBorderLeft)
{
SetBorderBrush(BorderAlign.Left);
excelCellStyle.CellBorderBrushLeft = CellBorderBrushLeft;
}
if (ShowBorderRight)
{
SetBorderBrush(BorderAlign.Right);
excelCellStyle.CellBorderBrushRight = CellBorderBrushRight;
}
if (ShowBorderBottom)
{
SetBorderBrush(BorderAlign.Bottom);
excelCellStyle.CellBorderBrushBottom = CellBorderBrushBottom;
}
if (ShowBorderTop || ShowBorderLeft || ShowBorderRight || ShowBorderBottom)
{
CellBorderThickness = new Thickness(1);
excelCellStyle.BorderThickness = excelCellStyle.CellBorderThickness = CellBorderThickness;
row.SetCellStyle(_flex.Columns[cell.Column], excelCellStyle);
}
}
private void AddBackgroundToCellFormat(ExcelCellStyle item, CellFormat cellFormat)
{
if (item.BackgroundColor != null)
{
var color = item.BackgroundColor.ColorHexCode;
var fill = new Fill
{
PatternFill = new PatternFill
{
ForegroundColor = new ForegroundColor {
Rgb = color
},
PatternType = PatternValues.Solid
}
};
cellFormat.FillId = GetElementIndexOrAdd(this.fills, fill);
cellFormat.ApplyFill = true;
}
}
public ExcelCellStyle GetStyle(int styleIndex)
{
if (inverseCache.TryGetValue((uint)styleIndex, out var result))
{
return(result);
}
var cellFormat = stylesheet?.CellFormats?.ChildElements?.Count > styleIndex ? (CellFormat)stylesheet.CellFormats.ChildElements[styleIndex] : null;
result = new ExcelCellStyle
{
FillStyle = cellFormat?.FillId == null ? null : GetCellFillStyle(cellFormat.FillId.Value),
FontStyle = cellFormat?.FontId == null ? null : GetCellFontStyle(cellFormat.FontId.Value),
NumberingFormat = cellFormat?.NumberFormatId == null ? null : GetCellNumberingFormat(cellFormat.NumberFormatId.Value),
BordersStyle = cellFormat?.BorderId == null ? null : GetCellBordersStyle(cellFormat.BorderId.Value),
Alignment = cellFormat?.Alignment == null ? null : GetCellAlignment(cellFormat.Alignment)
};
inverseCache.Add((uint)styleIndex, result);
return(result);
}
/// <summary>
/// 创建一个新列头对象
/// </summary>
/// <param name="columnName">列头名称</param>
/// <param name="dataPropertyName">数据属性名称</param>
/// <param name="with">列宽</param>
/// <param name="columnExcelCellStyle">列头的单元格式</param>
///<param name="defaultExcelCellStyle">列值的单元格式</param>
public ExcelColumn(string columnName, string dataPropertyName, int with, ExcelCellStyle columnExcelCellStyle, ExcelCellStyle defaultExcelCellStyle)
: this(columnName, dataPropertyName, with)
{
_defaultExcelCellStyle = defaultExcelCellStyle;
_columnExcelCellStyle = columnExcelCellStyle;
}
private string GetYdPayLogOnExport(DataTable dtSource)
{
string fn = "/XTemp/缴费明细单.xls";
string filePath = System.Web.Hosting.HostingEnvironment.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.Hosting.HostingEnvironment.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
if (dtSource == null || dtSource.Rows.Count == 0)
{
throw new Exception("没有数据");
}
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
Excel.ExcelOparete excel = new Excel.ExcelOparete("缴费明细单");
var cellStyle = new ExcelCellStyle();
cellStyle.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
cellStyle.FontSize = 12;
excel.SetObjectValue("缴费明细单", 0, 0, 1, cellStyle);
excel.SetObjectValue2("建筑名称", 1, 1, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["CoStrcName"], 1, 1, 1, 1);
excel.SetObjectValue2("房间名称", 2, 2, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["CoName"], 2, 2, 1, 1);
excel.SetObjectValue2("倍率", 3, 3, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Multiply"], 3, 3, 1, 1);
excel.SetObjectValue2("单价(元/kWh)", 4, 4, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Price"], 4, 4, 1, 1);
excel.SetObjectValue2("充值类型", 5, 5, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["IsPayS"], 5, 5, 1, 1);
excel.SetObjectValue2("充值方式", 6, 6, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["payTypeS"], 6, 6, 1, 1);
excel.SetObjectValue2("充值电量(kWh)", 7, 7, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["PayVal"], 7, 7, 1, 1);
excel.SetObjectValue2("充值金额(元)", 8, 8, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["PayAmt"], 8, 8, 1, 1);
excel.SetObjectValue2("金额(大写)", 9, 9, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["strAmt"], 9, 9, 1, 1);
excel.SetObjectValue2("操作者", 10, 10, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Create_by"], 10, 10, 1, 1);
excel.SetObjectValue2("操作时间", 11, 11, 0, 0);
excel.SetObjectValue2(dtSource.Rows[0]["Create_dt"], 11, 11, 1, 1);
excel.SaveExcelByFullFileName(filename);
return(fn);
}
/// <summary>
/// Adds the or updates the cell.
/// </summary>
/// <param name="address">The cell address.</param>
/// <param name="value">The cell data value.</param>
/// <param name="cellFormat">The cell data format.</param>
/// <param name="style">The cell style.</param>
public void AddOrUpdateCell(string address, object value, CellFormatEnum?cellFormat = null, ExcelCellStyle style = null)
{
var cell = new ExcelCell(address, value, cellFormat);
if (style != null)
{
cell.CellStyle = style;
}
if (this.Cells.ContainsKey(address))
{
this.Cells[address] = cell;
}
else
{
this.Cells.Add(address, cell);
}
}
private void AddBordersToCellFormat(ExcelCellStyle item, CellFormat cellFormat)
{
if (item.Borders != null && item.Borders.Count > 0)
{
var border = new Border();
foreach (var borderItem in item.Borders)
{
var color = new Color();
if (borderItem.Color is null)
{
color.Auto = true;
}
else
{
color.Rgb = borderItem.Color.ColorHexCode;
}
switch (borderItem.Border)
{
case ExcelCellBorderEnum.None:
break;
case ExcelCellBorderEnum.Left:
border.AppendChild(new LeftBorder {
Color = color, Style = (BorderStyleValues)(int)borderItem.Style
});
break;
case ExcelCellBorderEnum.Right:
border.AppendChild(new RightBorder {
Color = color, Style = (BorderStyleValues)(int)borderItem.Style
});
break;
case ExcelCellBorderEnum.Top:
border.AppendChild(new TopBorder {
Color = color, Style = (BorderStyleValues)(int)borderItem.Style
});
break;
case ExcelCellBorderEnum.Bottom:
border.AppendChild(new BottomBorder {
Color = color, Style = (BorderStyleValues)(int)borderItem.Style
});
break;
case ExcelCellBorderEnum.Diagonal:
border.AppendChild(new DiagonalBorder {
Color = color, Style = (BorderStyleValues)(int)borderItem.Style
});
break;
default:
throw new NotSupportedException();
}
}
cellFormat.BorderId = GetElementIndexOrAdd(this.borders, border);
cellFormat.ApplyBorder = true;
}
}
public ExcelValueObject(string text, ExcelCellFormat format, ExcelCellStyle style)
{
Text = text;
Format = format;
Style = style;
}
private void button1_Click(object sender, EventArgs e)
{
// 获取数据库数据
DataTable dataTable = new DataTable();
string saveFileName = "拌合站Excel";
/*新建表；新建Sheet并命名；设定cellStyle*/
XSSFWorkbook book = new XSSFWorkbook();
ISheet sheet1 = book.CreateSheet("Sheet1");
IRow headerRow4Sheet1 = sheet1.CreateRow(0);
ExcelCellStyle cellStyle = new ExcelCellStyle(book);
ICell cell;
try
{
if ("sqlserver".Equals(dbType.Text))
{
DBHelp db = new DBHelp();
db.Url = @"Data Source=" + url.Text + "," + port.Text + ";Initial Catalog=" + dbName.Text + ";User ID=" + username.Text + ";pwd=" + password.Text + "";
dataTable = db.adapterFind(sql.Text);
}
if ("mysql".Equals(dbType.Text))
{
MySqlUtil db = new MySqlUtil();
// db.Url = @"server=" + url.Text + ";port=" + port.Text + ";user="******";password="******"; database=" + dbName.Text + ";";
// db.Url = @"data source=" + url.Text + ";database=" + dbName.Text + ";user id=" + username.Text + ";password="******";pooling=false;charset=utf8;";
db.Url = @"Server=" + url.Text + ";port=" + port.Text + ";Database=" + dbName.Text + ";Uid=" + username.Text + ";Pwd=" + password.Text + ";CharSet=utf8;";
dataTable = db.adapterFind(sql.Text);
}
if ("oracle".Equals(dbType.Text))
{
OracleUtil db = new OracleUtil();
db.Url = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + url.Text + ")(PORT=" + port.Text + ")))(CONNECT_DATA=(SERVICE_NAME=" + dbName.Text + ")));User ID=" + username.Text + ";Password="******";";
dataTable = db.adapterFind(sql.Text);
}
/*设定标题行*/
int columnNum = 0;
columnNum = dataTable.Columns.Count;
string[] colNames = new string[columnNum];
for (int i = 0; i < dataTable.Columns.Count; i++)
{
colNames[i] = dataTable.Columns[i].ColumnName;
}
for (int i = 0; i < colNames.Length; i++)
{
cell = headerRow4Sheet1.CreateCell(i);
cell.CellStyle = cellStyle.style;
cell.SetCellValue(colNames[i]);
}
/*设定内容行*/
int sheet1RowID = 1;//从表格的第二2行开始进行循环
foreach (DataRow dataRow in dataTable.Rows)
{
IRow r = sheet1.CreateRow(sheet1RowID);
for (int i = 0; i < colNames.Length; i++)
{
cell = r.CreateCell(i);
cell.SetCellValue(dataRow[i].ToString().Trim());
cell.CellStyle = cellStyle.style;
}
sheet1RowID = sheet1RowID + 1;
}
/*单元格长度格式化*/
ChangeStyle(book, sheet1);
}
catch (Exception ex)
{
cell = headerRow4Sheet1.CreateCell(0);
cell.CellStyle = cellStyle.style;
cell.SetCellValue(string.Format("异常类型：{0}\r\n异常消息：{1}\r\n异常信息：{2}\r\n",
ex.GetType().Name, ex.Message, ex.StackTrace));
saveFileName = "查询出错请检查填写信息";
}
/*IO流输出保存*/
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = saveFileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
MemoryStream ms = new MemoryStream();
book.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
book.Write(file);
file.Close();
book = null;
ms.Close();
ms.Dispose();
}
private void ExportExcel()
{
List <Customer> listCustomer = GetCustomer("");
/*新建表；新建Sheet并命名；设定cellStyle*/
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet1 = book.CreateSheet("Sheet1");
IRow headerRow4Sheet1 = sheet1.CreateRow(0);
ExcelCellStyle cellStyle = new ExcelCellStyle(book);
ICell cell;
/*设定标题行*/
cell = headerRow4Sheet1.CreateCell(0);
cell.CellStyle = cellStyle.style;
cell.SetCellValue("客户代码");
cell = headerRow4Sheet1.CreateCell(1);
cell.CellStyle = cellStyle.style;
cell.SetCellValue("客户名");
cell = headerRow4Sheet1.CreateCell(2);
cell.CellStyle = cellStyle.style;
cell.SetCellValue("客户地址");
/*设定内容行*/
int sheet1RowID = 1;//从表格的第二2行开始进行循环
foreach (Customer customer in listCustomer)
{
IRow r = sheet1.CreateRow(sheet1RowID);
cell = r.CreateCell(0); cell.SetCellValue(customer.customerID); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(1); cell.SetCellValue(customer.customerName); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(2); cell.SetCellValue(customer.customerAddress); cell.CellStyle = cellStyle.style;
sheet1RowID = sheet1RowID + 1;
}
/*单元格长度格式化*/
ChangeStyle(book, sheet1);
/*IO流输出保存*/
string saveFileName = "客户列表导出Excel";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = saveFileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
MemoryStream ms = new MemoryStream();
book.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
book.Write(file);
file.Close();
book = null;
ms.Close();
ms.Dispose();
}
private static void CreateNewDocument()
{
var wb = new ExcelWorkbook();
var myFirstSheet = new ExcelSheet("My Sheet");
wb.AddSheet(myFirstSheet);
var boldStyle = new ExcelCellStyle
{
Font = new ExcelCellStyleFont
{
IsBold = true
}
};
var borderedYellowCell = new ExcelCellStyle
{
CellFormat = CellFormatEnum.PercentageTwoDecimals,
BackgroundColor = new ExcelColor(Color.Green),
Font = new ExcelCellStyleFont {
Color = new ExcelColor(Color.Red)
},
};
var greenCell = new ExcelCellStyle
{
BackgroundColor = new ExcelColor("00FF00"),
};
var bordersCell = new ExcelCellStyle();
bordersCell.Borders.Add(new ExcelCellStyleBorder(ExcelCellBorderEnum.Top, ExcelCellStyleBorderSizeEnum.Thick, new ExcelColor("FF0000")));
bordersCell.Borders.Add(new ExcelCellStyleBorder(ExcelCellBorderEnum.Bottom, ExcelCellStyleBorderSizeEnum.DashDotDot));
myFirstSheet.AddOrUpdateCell(new ExcelCell("A3", "Decimals")
{
CellStyle = boldStyle
});
myFirstSheet.AddOrUpdateCell(new ExcelCell("B3", "Percentages")
{
CellStyle = boldStyle
});
myFirstSheet.AddOrUpdateCell(new ExcelCell("C3", "C Column")
{
CellStyle = boldStyle
});
myFirstSheet.AddOrUpdateCell(new ExcelCell(4, 3, "D Column")
{
CellStyle = boldStyle
});
myFirstSheet.AddOrUpdateCell(new ExcelCell("A", 4, 0.34m, CellFormatEnum.DecimalTwoDecimals));
myFirstSheet.AddOrUpdateCell(new ExcelCell("B", 4, 0.231, CellFormatEnum.PercentageTwoDecimals));
myFirstSheet.AddOrUpdateCell(new ExcelCell("C", 4, DateTime.Now, CellFormatEnum.DateTime));
myFirstSheet.AddOrUpdateCell(new ExcelCell("D", 4, 0.55m)
{
CellStyle = borderedYellowCell
});
myFirstSheet.AddOrUpdateCell(new ExcelCell("A", 5, 0.10m, CellFormatEnum.DecimalTwoDecimals));
myFirstSheet.AddOrUpdateCell(new ExcelCell("B", 5, 0.20m, CellFormatEnum.PercentageTwoDecimals));
myFirstSheet.AddOrUpdateCell(new ExcelCell("C", 5, DateTime.Now, CellFormatEnum.DateTime));
myFirstSheet.AddOrUpdateCell(new ExcelCell("D", 5, 0.99m));
myFirstSheet.AddOrUpdateCell(new ExcelCell("A6", 30));
myFirstSheet.AddOrUpdateCell(new ExcelCell("B6", 20)
{
CellStyle = borderedYellowCell
});
myFirstSheet.AddOrUpdateCell(new ExcelCell("C6", 10)
{
CellStyle = greenCell
});
myFirstSheet.AddOrUpdateCell(new ExcelCell("D6", 55));
myFirstSheet.AddOrUpdateCell(new ExcelCell("C8", "=SUM(A6:D6)")
{
CellStyle = bordersCell
});
var xlsData = wb.Save();
var fileName = @"C:\temp\MyExcel_v2.xlsx";
File.WriteAllBytes(fileName, xlsData);
System.Diagnostics.Process.Start(fileName);
}