public TestXSSFChartAxis()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.BOTTOM);
}
public void TestLegendPositionAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
Assert.AreEqual(LegendPosition.TopRight, legend.Position);
}
public void Test_setOverlay_defaultChartLegend_expectOverlayInitialValueSetToFalse()
{
// Arrange
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
// Act
// Assert
Assert.IsFalse(legend.IsOverlay);
}
public void TestAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IValueAxis axis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
axis.SetCrossBetween(AxisCrossBetween.MidpointCategory);
Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MidpointCategory);
axis.Crosses=(AxisCrosses.AutoZero);
Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void TestAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IValueAxis axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.BOTTOM);
axis.SetCrossBetween(AxisCrossBetween.MIDPOINT_CATEGORY);
Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MIDPOINT_CATEGORY);
axis.SetCrosses(AxisCrosses.AUTO_ZERO);
Assert.AreEqual(axis.GetCrosses(), AxisCrosses.AUTO_ZERO);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
/// <summary>
/// 生成模板
/// </summary>
/// <param name="displayName">文件名</param>
/// <returns>生成的模版文件</returns>
public byte[] GenerateTemplate(out string displayName)
{
//设置导出的文件名称
string SheetName = !string.IsNullOrEmpty(FileDisplayName) ? FileDisplayName : this.GetType().Name;
displayName = SheetName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xlsx";
//1.声明Excel文档
IWorkbook workbook = new XSSFWorkbook();
//加载初始化数据和下拉菜单数据，可重载
InitExcelData();
//设置TemplateDataTable的各列的类型
CreateDataTable();
//设置初始化数据到DataTable中
SetTemplateDataValus();
//2.设置workbook的sheet页
ISheet sheet = workbook.CreateSheet();
workbook.SetSheetName(0, SheetName);
//3.设置Sheet页的Row
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 20;
ISheet enumSheet = workbook.CreateSheet();
IRow enumSheetRow1 = enumSheet.CreateRow(0);
enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]);
enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]);
enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号
ISheet dataSheet = workbook.CreateSheet();
#region 设置excel模板列头
//默认灰色
var headerStyle = GetCellStyle(workbook);
headerStyle.IsLocked = true;
//黄色
var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow);
yellowStyle.IsLocked = true;
//红色
var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red);
redStyle.IsLocked = true;
//取得所有ExcelPropety
var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
//设置列的索引
int _currentColunmIndex = 0;
//设置Excel是否需要保护，默认不保护
bool IsProtect = false;
//循环类的属性，赋值给列
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
//依次获取属性字段
ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
ColumnDataType dateType = (excelPropety.DataType == ColumnDataType.DateTime || excelPropety.DataType == ColumnDataType.Date) ? ColumnDataType.Text : excelPropety.DataType; //日期类型默认设置成Text类型,在赋值时会进行日期验证
//设置是否保护Excel
if (excelPropety.ReadOnly)
{
IsProtect = true;
}
//给必填项加星号
string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(colName);
//修改列头样式
switch (excelPropety.BackgroudColor)
{
case BackgroudColorEnum.Yellow:
row.Cells[_currentColunmIndex].CellStyle = yellowStyle;
break;
case BackgroudColorEnum.Red:
row.Cells[_currentColunmIndex].CellStyle = redStyle;
break;
default:
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
break;
}
var dataStyle = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
if (dateType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex];
string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName);
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
if (dynamicCol.ReadOnly)
{
IsProtect = true;
}
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置单元格样式及数据类型
dataStyle.IsLocked = excelPropety.ReadOnly;
dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
else
{
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置是否锁定
dataStyle.IsLocked = excelPropety.ReadOnly;
//设置单元格样式及数据类型
excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
#endregion
#region 添加模版数据
if (TemplateDataTable.Rows.Count > 0)
{
for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
{
DataRow tableRow = TemplateDataTable.Rows[i];
IRow dataRow = sheet.CreateRow(1 + i);
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
string colName = propetys[porpetyIndex].Name;
tableRow[colName].ToString();
dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString());
}
}
}
#endregion
//冻结行
sheet.CreateFreezePane(0, 1, 0, 1);
//锁定excel
if (IsProtect)
{
sheet.ProtectSheet("password");
}
//隐藏前2个Sheet
workbook.SetSheetHidden(1, SheetState.Hidden);
workbook.SetSheetHidden(2, SheetState.Hidden);
//返回byte数组
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
// 時間のかかる処理を行うメソッド
private string DoWork(IProgress <int> progress)
{
var allLines = File.ReadAllLines(sourceFile, Encoding.GetEncoding("shift-jis"));
string directoryName = Path.GetDirectoryName(sourceFile);
string fileName = Path.GetFileNameWithoutExtension(sourceFile) + ".xlsx";
string filePath = Path.Combine(directoryName, fileName);
IWorkbook book;
// ブック作成
book = new XSSFWorkbook();
// シート無しのExcelファイルは保存はできるが、開くとエラーが発生する。
book.CreateSheet("Sheet1");
using (FileStream fs = File.Create(filePath))
{
book.Write(fs);
fs.Close();
}
// シート設定
ISheet sheet = book.GetSheetAt(0);
sheet.AutoSizeColumn(0);
// 文字列に書式変更
var style = book.CreateCellStyle();
style.DataFormat = book.CreateDataFormat().GetFormat("@");
int n = allLines.Length;
string oneRow;
int row = 0;
foreach (var oneLine in allLines)
{
oneRow = oneLine.Replace("\"", "");
var items = oneRow.Split(',');
try
{
var dataRow = sheet.CreateRow(row);
int column = 0;
foreach (var item in items)
{
dataRow.CreateCell(column);
dataRow.Cells[column].SetCellValue(item);
dataRow.Cells[column].CellStyle = style;
// 幅の自動調整（時間がかかる）
//sheet.AutoSizeColumn(column);
column++;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
row++;
Debug.WriteLine(String.Format("行: {0}/{1}", row, allLines.Length));
// 進捗率
int percentage = row * 100 / n;
progress.Report(percentage);
// 10000行ごとに保存
//if (row % 10000 == 0)
//{
// try
// {
// // Excelファイルを保存
// using (FileStream fs = File.Create(filePath))
// {
// book.Write(fs);
// fs.Close();
// }
// sheet = book.GetSheetAt(0);
// }
// catch (Exception ex)
// {
// Debug.WriteLine(ex);
// }
//}
// 20000行で終了
//if (row % 20000 == 0)
//{
// break;
//}
}
// Excelファイルを保存
using (FileStream fs = File.Create(filePath))
{
book.Write(fs);
fs.Close();
}
// このメソッドからの戻り値
return("完了");
}
public ActionResult index(string answer)
{
string sWebRootFolder = HttpRuntime.AppDomainAppPath;
string sFileName = @"Test.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Url.Scheme, Request.Url.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Test");
IRow row = excelSheet.CreateRow(0);
int j = 1;
if (answer.Equals("Export All"))
{
int x = 0;
row.CreateCell(x++).SetCellValue("Employee ID");
row.CreateCell(x++).SetCellValue("Last Name");
row.CreateCell(x++).SetCellValue("First Name");
row.CreateCell(x++).SetCellValue("Date");
row.CreateCell(x++).SetCellValue("Clock In Time");
row.CreateCell(x++).SetCellValue("Clock Out Time");
row.CreateCell(x++).SetCellValue("Hours Worked");
row.CreateCell(x++).SetCellValue("Time Type");
row.CreateCell(x++).SetCellValue("Overtime Hours");
row.CreateCell(x++).SetCellValue("PTO Earned");
row.CreateCell(x++).SetCellValue("");
row.CreateCell(x++).SetCellValue("");
var time = (db.TIME_SHEET_ENTRY).ToList();
foreach (var entry in time)
{
string fname = db.USERs.Where(w => w.userID == entry.employee).Select(w => w.fname).FirstOrDefault();
string lname = db.USERs.Where(w => w.userID == entry.employee).Select(w => w.lname).FirstOrDefault();
row = excelSheet.CreateRow(j);
int i = 0;
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.employee));
row.CreateCell(i++).SetCellValue(lname);
row.CreateCell(i++).SetCellValue(fname);
if (entry.date.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.date));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.date));
}
if (entry.clock_in_time.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.clock_in_time));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.clock_in_time));
}
if (entry.clock_out_time.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.clock_out_time));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.clock_out_time));
}
if (entry.hours_worked.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.hours_worked));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.hours_worked));
}
row.CreateCell(i++).SetCellValue(entry.time_type);
if (entry.overtime_hours_worked.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.overtime_hours_worked));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.overtime_hours_worked));
}
if (entry.pto_earned.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.pto_earned));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.pto_earned));
}
j++;
}
}
if (answer.Equals("Export Range Entries"))
{
int x = 0;
row.CreateCell(x++).SetCellValue("Employee ID");
row.CreateCell(x++).SetCellValue("Last Name");
row.CreateCell(x++).SetCellValue("First Name");
row.CreateCell(x++).SetCellValue("Date");
row.CreateCell(x++).SetCellValue("Clock In Time");
row.CreateCell(x++).SetCellValue("Clock Out Time");
row.CreateCell(x++).SetCellValue("Hours Worked");
row.CreateCell(x++).SetCellValue("Time Type");
row.CreateCell(x++).SetCellValue("Overtime Hours");
row.CreateCell(x++).SetCellValue("PTO Earned");
row.CreateCell(x++).SetCellValue("");
row.CreateCell(x++).SetCellValue("");
DateTime date1 = new DateTime(2018, 11, 13);
DateTime date2 = new DateTime(2018, 11, 24);
var time = db.TIME_SHEET_ENTRY.Where(i => i.date >= date1 && i.date <= date2).ToList();
foreach (var entry in time)
{
string fname = db.USERs.Where(w => w.userID == entry.employee).Select(w => w.fname).FirstOrDefault();
string lname = db.USERs.Where(w => w.userID == entry.employee).Select(w => w.lname).FirstOrDefault();
row = excelSheet.CreateRow(j);
int i = 0;
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.employee));
row.CreateCell(i++).SetCellValue(lname);
row.CreateCell(i++).SetCellValue(fname);
if (entry.date.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.date));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.date));
}
if (entry.clock_in_time.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.clock_in_time));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.clock_in_time));
}
if (entry.clock_out_time.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.clock_out_time));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.clock_out_time));
}
if (entry.hours_worked.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.hours_worked));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.hours_worked));
}
row.CreateCell(i++).SetCellValue(entry.time_type);
if (entry.overtime_hours_worked.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.overtime_hours_worked));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.overtime_hours_worked));
}
if (entry.pto_earned.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.pto_earned));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.pto_earned));
}
j++;
}
}
if (answer.Equals("Export Range"))
{
int x = 0;
row.CreateCell(x++).SetCellValue("Employee ID");
row.CreateCell(x++).SetCellValue("Last Name");
row.CreateCell(x++).SetCellValue("First Name");
row.CreateCell(x++).SetCellValue("Total Entries");
row.CreateCell(x++).SetCellValue("Total Hours");
row.CreateCell(x++).SetCellValue("Total Overtime");
row.CreateCell(x++).SetCellValue("Total PTO Earned");
row.CreateCell(x++).SetCellValue("Total PTO Used");
row.CreateCell(x++).SetCellValue("Total Unpaid Time");
row.CreateCell(x++).SetCellValue("");
row.CreateCell(x++).SetCellValue("");
DateTime date1 = new DateTime(2018, 11, 13);
DateTime date2 = new DateTime(2018, 11, 24);
var periodRange = db.PAY_PERIOD.Where(i => i.start_date >= date1 && i.start_date <= date2).ToList();
foreach (var period in periodRange)
{
var sheets = db.TIME_SHEET.Where(i => i.active).ToList();
foreach (var entry in sheets)
{
row = excelSheet.CreateRow(j);
string fname = db.USERs.Where(w => w.userID == entry.employee).Select(w => w.fname).FirstOrDefault();
string lname = db.USERs.Where(w => w.userID == entry.employee).Select(w => w.lname).FirstOrDefault();
int i = 0;
row.CreateCell(i++).SetCellValue(entry.employee);
row.CreateCell(i++).SetCellValue(lname);
row.CreateCell(i++).SetCellValue(fname);
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.total_entries));
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.total_hours_worked));
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.total_overtime_worked));
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.total_pto_earned));
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.total_pto_used));
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.total_unpaid_time));
j++;
}
}
}
workbook.Write(fs);
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
stream.CopyTo(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFTextParagraph para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 1");
List <XSSFTextRun> Runs = para.TextRuns;
Assert.AreEqual(1, Runs.Count);
XSSFTextRun run = Runs[0];
Assert.AreEqual("Line 1", run.Text);
//Assert.IsNotNull(run.ParentParagraph);
//Assert.IsNotNull(run.XmlObject);
Assert.IsNotNull(run.GetRPr());
Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor);
Color color = Color.FromArgb(0, 255, 255);
run.FontColor = (/*setter*/ color);
Assert.AreEqual(color, run.FontColor);
Assert.AreEqual(11.0, run.FontSize, 0.01);
run.FontSize = (/*setter*/ 12.32);
Assert.AreEqual(12.32, run.FontSize, 0.01);
run.FontSize = (/*setter*/ -1.0);
Assert.AreEqual(11.0, run.FontSize, 0.01);
run.FontSize = (/*setter*/ -1.0);
Assert.AreEqual(11.0, run.FontSize, 0.01);
try
{
run.FontSize = (/*setter*/ 0.9);
Assert.Fail("Should fail");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.Contains("0.9"));
}
Assert.AreEqual(11.0, run.FontSize, 0.01);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 12.31);
Assert.AreEqual(12.31, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 0.0);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 0.0);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, (byte)1, false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", unchecked ((byte)-1), (byte)1, false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, unchecked ((byte)-1), false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, (byte)1, true);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily(null, (byte)1, (byte)1, false);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFontFamily(null, (byte)1, (byte)1, false);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFont("Arial");
Assert.AreEqual("Arial", run.FontFamily);
Assert.AreEqual((byte)0, run.PitchAndFamily);
run.SetFont(null);
Assert.AreEqual((byte)0, run.PitchAndFamily);
Assert.IsFalse(run.IsStrikethrough);
run.IsStrikethrough = (/*setter*/ true);
Assert.IsTrue(run.IsStrikethrough);
run.IsStrikethrough = (/*setter*/ false);
Assert.IsFalse(run.IsStrikethrough);
Assert.IsFalse(run.IsSuperscript);
run.IsSuperscript = (/*setter*/ true);
Assert.IsTrue(run.IsSuperscript);
run.IsSuperscript = (/*setter*/ false);
Assert.IsFalse(run.IsSuperscript);
Assert.IsFalse(run.IsSubscript);
run.IsSubscript = (/*setter*/ true);
Assert.IsTrue(run.IsSubscript);
run.IsSubscript = (/*setter*/ false);
Assert.IsFalse(run.IsSubscript);
Assert.AreEqual(TextCap.NONE, run.TextCap);
Assert.IsFalse(run.IsBold);
run.IsBold = (/*setter*/ true);
Assert.IsTrue(run.IsBold);
run.IsBold = (/*setter*/ false);
Assert.IsFalse(run.IsBold);
Assert.IsFalse(run.IsItalic);
run.IsItalic = (/*setter*/ true);
Assert.IsTrue(run.IsItalic);
run.IsItalic = (/*setter*/ false);
Assert.IsFalse(run.IsItalic);
Assert.IsFalse(run.IsUnderline);
run.IsUnderline = (/*setter*/ true);
Assert.IsTrue(run.IsUnderline);
run.IsUnderline = (/*setter*/ false);
Assert.IsFalse(run.IsUnderline);
Assert.IsNotNull(run.ToString());
}
finally
{
wb.Close();
}
}
public void Should_Help_To_Debug_NPOIFormat()
{
var wb = new XSSFWorkbook();
short colorBlack = IndexedColors.Black.Index;
short colorLightBlue = IndexedColors.LightBlue.Index;
short colorLightOrange = IndexedColors.LightOrange.Index;
short colorGreen = IndexedColors.Green.Index;
var sheet = wb.CreateSheet();
var row0 = sheet.CreateRow(0);
var cell0 = row0.CreateCell(0);
cell0.SetCellValue("FIRST");
var cell1 = row0.CreateCell(1);
cell1.SetCellValue("SECOND");
// Best way to clone Font: Wrap its creation in a method
// Because the existing method IFont.CloneStyleFrom(IFont) is buggy
IFont GetFont1()
{
var f = wb.CreateFont();
f.Color = colorBlack;
f.FontHeightInPoints = 14;
f.IsBold = true;
return(f);
}
var s1 = wb.CreateCellStyle();
s1.FillPattern = FillPattern.SolidForeground;
s1.FillForegroundColor = colorLightBlue;
s1.VerticalAlignment = VerticalAlignment.Bottom;
var f1 = GetFont1();
s1.SetFont(f1);
cell0.CellStyle = s1;
var s2 = wb.CreateCellStyle();
s2.CloneStyleFrom(s1);
s2.FillForegroundColor = colorLightOrange;
s2.Rotation = 49;
s1.VerticalAlignment = VerticalAlignment.Top;
var f2 = GetFont1();
f2.IsBold = false;
f2.IsItalic = true;
f2.Color = colorGreen;
s2.SetFont(f2);
cell1.CellStyle = s2;
TestUtils.WriteDebugFile(wb, FILE_DEBUG2);
using (var ms = new MemoryStream())
{
wb.Write(ms);
var readWb = new XSSFWorkbook(new MemoryStream(ms.ToArray()));
var readSheet = readWb.GetSheetAt(0);
var rs1 = readSheet.GetRow(0).GetCell(0).CellStyle;
Check.That(rs1).IsNotNull();
Check.That(rs1.FillForegroundColor).IsEqualTo(colorLightBlue);
Check.That(rs1.GetFont(readWb).IsBold).IsTrue();
Check.That(rs1.GetFont(readWb).IsItalic).IsFalse();
Check.That(rs1.GetFont(readWb).Color).IsEqualTo(colorBlack);
var rs2 = readSheet.GetRow(0).GetCell(1).CellStyle;
Check.That(rs2.GetFont(readWb).IsBold).IsFalse();
Check.That(rs2.GetFont(readWb).IsItalic).IsTrue();
Check.That(rs2.GetFont(readWb).Color).IsEqualTo(colorGreen);
}
}
static public void SaveSmallAudit(string path, string pathSaveFile, IEnumerable <ListDisplay.ListItem> data,
DateTime?dateFrom, DateTime?dateTo, string sumPaid)
{
XSSFWorkbook xlWorkBook = new XSSFWorkbook();
ISheet sheet = xlWorkBook.CreateSheet("1");
IRow firstRow, row;
firstRow = sheet.CreateRow(0);
row = sheet.CreateRow(1);
row.CreateCell(0).SetCellValue("Data od");
row.CreateCell(2).SetCellValue("Data do");
if (dateTo == null)
{
row.CreateCell(3).SetCellValue(DateTime.Parse(data.Last().ItemDate).ToShortDateString());
}
else
{
row.CreateCell(3).SetCellValue(dateTo.Value.ToShortDateString());
}
if (dateFrom == null)
{
row.CreateCell(1).SetCellValue(DateTime.Parse(data.First().ItemDate).ToShortDateString());
}
else
{
row.CreateCell(1).SetCellValue(dateFrom.Value.ToShortDateString());
}
row = sheet.CreateRow(2);
row.CreateCell(0).SetCellValue("Kod pracownika");
row.CreateCell(1).SetCellValue("ID");
row.CreateCell(2).SetCellValue("Televend ID");
row.CreateCell(3).SetCellValue("Suma zakupów");
List <string> IDs = DatabaseHandling.GetCardIDs(path);
int i;
int rowCounter = 3;
IEnumerable <ListDisplay.ListItem> currentIDItem;
for (i = 0; i < IDs.Count(); i++)
{
currentIDItem = (data as IEnumerable <ListDisplay.ListItem>).Where(item => item.ItemID == IDs[i]);
if (currentIDItem.FirstOrDefault().ItemID == null)
{
continue;
}
string currentSum = currentIDItem.Sum(item => Convert.ToDecimal(item.ItemPurchase)).ToString();
if (currentSum == "0")
{
continue;
}
row = sheet.CreateRow(rowCounter);
row.CreateCell(0).SetCellValue(currentIDItem.FirstOrDefault().ItemWorkerID);
row.CreateCell(2).SetCellValue(IDs[i]);
row.CreateCell(1).SetCellValue(currentIDItem.FirstOrDefault().ItemStickerID);
row.CreateCell(3).SetCellValue(currentSum);
rowCounter++;
}
row = sheet.CreateRow(i + 3);
row.CreateCell(0).SetCellValue("Suma");
row.CreateCell(3).SetCellValue(sumPaid);
for (int j = 0; j < 4; j++)
{
sheet.AutoSizeColumn(j);
}
//Add after auto sizing to avoid enormous first column
firstRow.CreateCell(0).SetCellValue("Wyciąg z audytu wygenerowany " + DateTime.Now);
if (!pathSaveFile.EndsWith(".xlsx"))
{
pathSaveFile += ".xlsx";
}
using (var fs = new FileStream(pathSaveFile, FileMode.Create, FileAccess.Write))
{
xlWorkBook.Write(fs);
}
}
public void SetUp()
{
wb = new XSSFWorkbook();
wb.CreateSheet(VALID_SHEET_NAME);
}
public void ExcelExport()
{
Search();
//建立Excel 2003檔案
IWorkbook wb = new XSSFWorkbook();
ISheet ws;
dt = ds.Tables[tablename];
if (dt.TableName != string.Empty)
{
ws = wb.CreateSheet(dt.TableName);
}
else
{
ws = wb.CreateSheet("Sheet1");
}
ws.CreateRow(0);//第一行為欄位名稱
for (int i = 0; i < dt.Columns.Count; i++)
{
ws.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
int j = 0;
if (tablename.Equals("TEMPds1"))
{
foreach (DataGridViewRow dr in this.dataGridView1.Rows)
{
ws.CreateRow(j + 1);
ws.GetRow(j + 1).CreateCell(0).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[0].ToString());
ws.GetRow(j + 1).CreateCell(1).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[1].ToString());
ws.GetRow(j + 1).CreateCell(2).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[2].ToString());
ws.GetRow(j + 1).CreateCell(3).SetCellValue(Convert.ToDouble(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[3].ToString()));
j++;
}
}
else if (tablename.Equals("TEMPds2"))
{
foreach (DataGridViewRow dr in this.dataGridView1.Rows)
{
ws.CreateRow(j + 1);
ws.GetRow(j + 1).CreateCell(0).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[0].ToString());
ws.GetRow(j + 1).CreateCell(1).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[1].ToString());
ws.GetRow(j + 1).CreateCell(2).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[2].ToString());
ws.GetRow(j + 1).CreateCell(3).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[3].ToString());
ws.GetRow(j + 1).CreateCell(4).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[4].ToString());
ws.GetRow(j + 1).CreateCell(5).SetCellValue(Convert.ToDouble(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[5].ToString()));
ws.GetRow(j + 1).CreateCell(6).SetCellValue(Convert.ToDouble(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[6].ToString()));
ws.GetRow(j + 1).CreateCell(7).SetCellValue(Convert.ToDouble(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[7].ToString()));
j++;
}
}
else if (tablename.Equals("TEMPds3"))
{
foreach (DataGridViewRow dr in this.dataGridView1.Rows)
{
ws.CreateRow(j + 1);
ws.GetRow(j + 1).CreateCell(0).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[0].ToString());
ws.GetRow(j + 1).CreateCell(1).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[1].ToString());
ws.GetRow(j + 1).CreateCell(2).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[2].ToString());
ws.GetRow(j + 1).CreateCell(3).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[3].ToString());
ws.GetRow(j + 1).CreateCell(4).SetCellValue(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[4].ToString());
ws.GetRow(j + 1).CreateCell(5).SetCellValue(Convert.ToDouble(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[5].ToString()));
ws.GetRow(j + 1).CreateCell(6).SetCellValue(Convert.ToDouble(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[6].ToString()));
ws.GetRow(j + 1).CreateCell(7).SetCellValue(Convert.ToDouble(((System.Data.DataRowView)(dr.DataBoundItem)).Row.ItemArray[7].ToString()));
j++;
}
}
if (Directory.Exists(@"c:\temp\"))
{
//資料夾存在
}
else
{
//新增資料夾
Directory.CreateDirectory(@"c:\temp\");
}
StringBuilder filename = new StringBuilder();
filename.AppendFormat(@"c:\temp\查詢{0}.xlsx", DateTime.Now.ToString("yyyyMMdd"));
FileStream file = new FileStream(filename.ToString(), FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
MessageBox.Show("匯出完成-EXCEL放在-" + filename.ToString());
FileInfo fi = new FileInfo(filename.ToString());
if (fi.Exists)
{
System.Diagnostics.Process.Start(filename.ToString());
}
else
{
//file doesn't exist
}
}
static public void SaveBigAudit(string pathDB, string pathSaveFile, IEnumerable <ListDisplay.ListItem> data,
DateTime?dateFrom, DateTime?dateTo, string sumPaid)
{
XSSFWorkbook xlWorkBook = new XSSFWorkbook();
ISheet sheet = xlWorkBook.CreateSheet("1");
IRow firstRow, row;
firstRow = sheet.CreateRow(0);
row = sheet.CreateRow(1);
row.CreateCell(0).SetCellValue("Data od");
row.CreateCell(2).SetCellValue("Data do");
if (dateTo == null)
{
row.CreateCell(3).SetCellValue(DateTime.Parse(data.Last().ItemDate).ToShortDateString());
}
else
{
row.CreateCell(3).SetCellValue(dateTo.Value.ToShortDateString());
}
if (dateFrom == null)
{
row.CreateCell(1).SetCellValue(DateTime.Parse(data.First().ItemDate).ToShortDateString());
}
else
{
row.CreateCell(1).SetCellValue(dateFrom.Value.ToShortDateString());
}
row = sheet.CreateRow(2);
row.CreateCell(0).SetCellValue("Kod pracownika");
row.CreateCell(1).SetCellValue("ID");
row.CreateCell(2).SetCellValue("Televend ID");
row.CreateCell(3).SetCellValue("Data");
row.CreateCell(4).SetCellValue("Cena");
row.CreateCell(5).SetCellValue("Produkt");
row.CreateCell(6).SetCellValue("Maszyna");
int i;
for (i = 0; i < data.Count(); i++)
{
row = sheet.CreateRow(i + 3);
row.CreateCell(0).SetCellValue(data.ToList()[i].ItemWorkerID);
row.CreateCell(2).SetCellValue(data.ToList()[i].ItemID);
row.CreateCell(1).SetCellValue(data.ToList()[i].ItemStickerID);
row.CreateCell(3).SetCellValue(data.ToList()[i].ItemDate);
row.CreateCell(4).SetCellValue(data.ToList()[i].ItemPurchase);
row.CreateCell(5).SetCellValue(data.ToList()[i].ItemProduct);
row.CreateCell(6).SetCellValue(data.ToList()[i].ItemMachine);
}
row = sheet.CreateRow(i + 3);
row.CreateCell(0).SetCellValue("Suma");
row.CreateCell(4).SetCellValue(sumPaid);
for (int j = 0; j < 7; j++)
{
sheet.AutoSizeColumn(j);
}
//Add after auto sizing to avoid enormous first column
firstRow.CreateCell(0).SetCellValue("Wyciąg z audytu wygenerowany " + DateTime.Now);
if (!pathSaveFile.EndsWith(".xlsx"))
{
pathSaveFile += ".xlsx";
}
using (var fs = new FileStream(pathSaveFile, FileMode.Create, FileAccess.Write))
{
xlWorkBook.Write(fs);
}
}
private void DgvToXlsx(string fileName, DataGridView dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2007格式文件（*.xlsx）|*.xlsx";
sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssms");
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(fileName);
XSSFRow headRow = (XSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
XSSFCell headCell = (XSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
}
}
}
for (int i = 0; i < dgv.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
#endregion
MessageBox.Show("恭喜，导出成功");
}
public MemoryStream GenerateExcel(List <LogPresentationStub> items)
{
//kamus lokal
int rowIndex = 0, colIndex;
XSSFCellStyle styleHeader, styleDate; XSSFFont font;
XSSFRow row; XSSFCell cell;
//algoritma
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("activity log");
//create row (header)
row = (XSSFRow)sheet.CreateRow((short)rowIndex++);
//header style
styleHeader = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;;
styleHeader.SetFont(font);
//header data
List <string> colNames = new List <string> {
"id", "timestamp", "application", "ip", "user", "action", "data"
};
colIndex = 0;
foreach (string single in colNames)
{
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single);
cell.CellStyle = styleHeader;
}
//body
styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd HH:mm");
foreach (LogPresentationStub single in items)
{
row = (XSSFRow)sheet.CreateRow((short)rowIndex++);
colIndex = 0;
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single.Id);
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single.Timestamp);
cell.CellStyle = styleDate;
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single.Application);
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single.Ip);
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single.User);
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single.Action);
cell = (XSSFCell)row.CreateCell(colIndex++);
cell.SetCellValue(single.Data);
}
//write to file
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms);
}
public async Task <IActionResult> ExportUf()
{
try
{
string webRootPath = _hostingEnvironment.WebRootPath;
string fileName = "export_ufs.xlsx";
List <UfDomain> ufs = new List <UfDomain>()
{
new UfDomain(1, "Uf1", "Sigla1"),
new UfDomain(2, "Uf2", "Sigla2"),
new UfDomain(3, "Uf3", "Sigla3"),
new UfDomain(4, "Uf4", "Sigla4"),
new UfDomain(5, "Uf5", "Sigla5"),
};
DataTable dataTable = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(ufs), (typeof(DataTable)));
var memoryStream = new MemoryStream();
using (var fs = new FileStream(Path.Combine(webRootPath, fileName), FileMode.Create))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Ufs");
List <string> columns = new List <string>();
IRow row = sheet.CreateRow(0);
int columnIndex = 0;
int rowIndex = 1;
foreach (DataColumn colum in dataTable.Columns)
{
columns.Add(colum.ColumnName);
row.CreateCell(columnIndex).SetCellValue(colum.ColumnName);
columnIndex++;
}
foreach (DataRow drow in dataTable.Rows)
{
int cellIndex = 0;
row = sheet.CreateRow(rowIndex);
foreach (string column in columns)
{
row.CreateCell(cellIndex).SetCellValue(drow[column].ToString());
cellIndex++;
}
rowIndex++;
}
workbook.Write(fs);
}
using (var fileStream = new FileStream(Path.Combine(webRootPath, fileName), FileMode.Open))
{
await fileStream.CopyToAsync(memoryStream);
}
memoryStream.Position = 0;
return(File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
private IManualLayout GetEmptyLayout()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
return legend.GetManualLayout();
}
private void OverzichtButton_Click(object sender, RoutedEventArgs e)
{
Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
dlg.FileName = "Inventaris_Overzicht";
dlg.DefaultExt = ".xlsx";
dlg.Filter = "Excel file (.xlsx) | *.xlsx";
Nullable <bool> result = dlg.ShowDialog();
if (result == true)
{
string filename = dlg.FileName;
XSSFWorkbook wb;
XSSFSheet coldSheet;
XSSFSheet alcoholSheet;
wb = new XSSFWorkbook();
coldSheet = (XSSFSheet)wb.CreateSheet("fris");
alcoholSheet = (XSSFSheet)wb.CreateSheet("alcohol");
var r1c = coldSheet.CreateRow(0);
r1c.CreateCell(0).SetCellValue("Export Datum:");
r1c.CreateCell(1).SetCellValue(DateTime.Now.ToString("dd/MM/yyyy"));
var r2c = coldSheet.CreateRow(1);
r2c.CreateCell(0).SetCellValue("Drank");
r2c.CreateCell(1).SetCellValue("Op vooraad");
r2c.CreateCell(2).SetCellValue("Quotem");
r2c.CreateCell(3).SetCellValue("Te bestellen");
var r1a = alcoholSheet.CreateRow(0);
r1a.CreateCell(0).SetCellValue("Export Datum:");
r1a.CreateCell(1).SetCellValue(DateTime.Now.ToString("dd/MM/yyyy"));
var r2a = alcoholSheet.CreateRow(1);
r2a.CreateCell(0).SetCellValue("Drank");
r2a.CreateCell(1).SetCellValue("Op vooraad");
r2a.CreateCell(2).SetCellValue("Quotem");
r2a.CreateCell(3).SetCellValue("Te bestellen");
int count = 2;
foreach (DrinkItem i in coldDrinks)
{
int quote = qcoldDrink[count - 2];
var row = coldSheet.CreateRow(count);
row.CreateCell(0).SetCellValue(i.getName());
row.CreateCell(1).SetCellValue(i.getAmount());
row.CreateCell(2).SetCellValue(quote);
row.CreateCell(3).SetCellValue(quote - i.getAmount());
count = count + 1;
}
count = 2;
foreach (DrinkItem i in alcoholDrinks)
{
int quote = qalcohol[count - 2];
var row = alcoholSheet.CreateRow(count);
row.CreateCell(0).SetCellValue(i.getName());
row.CreateCell(1).SetCellValue(i.getAmount());
row.CreateCell(2).SetCellValue(quote);
row.CreateCell(3).SetCellValue(quote - i.getAmount());
count = count + 1;
}
for (int i = 0; i < 11; i++)
{
coldSheet.AutoSizeColumn(i);
alcoholSheet.AutoSizeColumn(i);
}
using (var fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
}
public async Task <IActionResult> Export()
{
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = @"Employees.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("employee");
IRow row = excelSheet.CreateRow(0);
row.CreateCell(0).SetCellValue("EmployeeId");
row.CreateCell(1).SetCellValue("EmployeeName");
row.CreateCell(2).SetCellValue("Age");
row.CreateCell(3).SetCellValue("Sex");
row.CreateCell(4).SetCellValue("Designation");
row = excelSheet.CreateRow(1);
row.CreateCell(0).SetCellValue(1);
row.CreateCell(1).SetCellValue("Jack Supreu");
row.CreateCell(2).SetCellValue(45);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Solution Architect");
row = excelSheet.CreateRow(2);
row.CreateCell(0).SetCellValue(2);
row.CreateCell(1).SetCellValue("Steve khan");
row.CreateCell(2).SetCellValue(33);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Software Engineer");
row = excelSheet.CreateRow(3);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellValue("Romi gill");
row.CreateCell(2).SetCellValue(25);
row.CreateCell(3).SetCellValue("FeMale");
row.CreateCell(4).SetCellValue("Junior Consultant");
row = excelSheet.CreateRow(4);
row.CreateCell(0).SetCellValue(4);
row.CreateCell(1).SetCellValue("Hider Ali");
row.CreateCell(2).SetCellValue(34);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Accountant");
row = excelSheet.CreateRow(5);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellValue("Mathew");
row.CreateCell(2).SetCellValue(48);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Human Resource");
workbook.Write(fs);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
public void ExportExcel(DataTable dt)
{
IWorkbook workbook;
string extension = ".xlsx";
workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
// make a header row
IRow row1 = sheet1.CreateRow(0);
var boldFont = workbook.CreateFont();
boldFont.FontHeightInPoints = 11;
boldFont.FontName = "Zawgyi-One";
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
// String columnName = dt.Columns[j].ToString();
cell.SetCellValue(getColumnHeaderName(j));
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(boldFont);
sheet1.AutoSizeColumn(j);
}
// loops through data
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
string cellValue = "";
switch (dt.Rows[i][columnName].GetType().ToString())
{
case "System.DBNull":
cellValue = "";
break;
default:
cellValue = dt.Rows[i][columnName].ToString();
//cellValue = dt.Rows[i][columnName].ToString() == "" ? "-" : dt.Rows[i][columnName].ToString();
break;
}
cell.SetCellValue(cellValue);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(boldFont);
}
}
using (var exportData = new MemoryStream())
{
Response.Clear();
workbook.Write(exportData);
if (extension == ".xlsx") //xlsx file format
{
string attachment = "attachment; filename=Item List.xlsx";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(exportData.ToArray());
}
else if (extension == "xls") //xls file format
{
string attachment = "attachment; filename=Item List.xlx";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(exportData.ToArray());
}
Response.End();
}
}
public IActionResult Export()
{
if (!AuthenticationController.CheckToken(HttpContext, out string _))
{
return(Unauthorized());
}
var stream = new MemoryStream();
var excelFile = new XSSFWorkbook();
var creationHelper = excelFile.GetCreationHelper();
var textStyle = excelFile.CreateCellStyle();
textStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat(@"@");
var upcStyle = excelFile.CreateCellStyle();
upcStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat(@"0");
var currencyStyle = excelFile.CreateCellStyle();
currencyStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat(@"$#,##0.00_);[Red]($#,##0.00)");
var dateStyle = excelFile.CreateCellStyle();
dateStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat(@"[$-en-US]m/d/yy h:mm AM/PM;@");
var itemTypesSheet = excelFile.CreateSheet("ItemTypes");
// add the header rows
var headerRow = itemTypesSheet.CreateRow(0);
var cell = headerRow.CreateCell(0);
cell.SetCellValue("Name");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(1);
cell.SetCellValue("UPC");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(2);
cell.SetCellValue("ImageURL");
cell.CellStyle = textStyle;
var itemsSheet = excelFile.CreateSheet("Items");
headerRow = itemsSheet.CreateRow(0);
cell = headerRow.CreateCell(0);
cell.SetCellValue("Id");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(1);
cell.SetCellValue("ItemTypeUPC");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(2);
cell.SetCellValue("OrderNumberToSeller");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(3);
cell.SetCellValue("OrderNumberToBuyer");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(4);
cell.SetCellValue("QRCode");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(5);
cell.SetCellValue("ItemStatus");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(6);
cell.SetCellValue("PricePaidBySeller");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(7);
cell.SetCellValue("TaxPaidBySeller");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(8);
cell.SetCellValue("ShippingCostToSeller");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(9);
cell.SetCellValue("ShippingCostToBuyer");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(10);
cell.SetCellValue("Fees");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(11);
cell.SetCellValue("OtherExpenses");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(12);
cell.SetCellValue("ShippingPaidByBuyer");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(13);
cell.SetCellValue("PricePaidByBuyer");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(14);
cell.SetCellValue("TimeStampOrdered");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(15);
cell.SetCellValue("TimeStampReceived");
cell.CellStyle = textStyle;
cell = headerRow.CreateCell(16);
cell.SetCellValue("TimeStampSold");
cell.CellStyle = textStyle;
using (var db = new DatabaseAccess())
{
var itemTypes = db.ItemTypes.ToArray();
for (int i = 0; i < itemTypes.Length; i++)
{
var row = itemTypesSheet.CreateRow(i + 1);
var itemType = itemTypes[i];
cell = row.CreateCell(0);
cell.SetCellValue(itemType.Name ?? "");
cell.CellStyle = textStyle;
cell = row.CreateCell(1);
cell.SetCellValue(itemType.UPC.ToString());
cell.CellStyle = upcStyle;
cell = row.CreateCell(2);
cell.SetCellValue(itemType.ImageURL ?? "");
cell.CellStyle = textStyle;
}
var items = db.Items.ToArray();
for (int i = 0; i < items.Length; i++)
{
var row = itemsSheet.CreateRow(i + 1);
var item = items[i];
cell = row.CreateCell(0);
cell.SetCellValue(item.Id.ToString());
cell.CellStyle = upcStyle;
cell = row.CreateCell(1);
cell.SetCellValue(item.ItemTypeUPC.ToString());
cell.CellStyle = upcStyle;
cell = row.CreateCell(2);
cell.SetCellValue(item.OrderNumberToSeller ?? "");
cell.CellStyle = textStyle;
cell = row.CreateCell(3);
cell.SetCellValue(item.OrderNumberToBuyer ?? "");
cell.CellStyle = textStyle;
cell = row.CreateCell(4);
cell.SetCellValue(item.QRCode ?? "");
cell.CellStyle = textStyle;
cell = row.CreateCell(5);
cell.SetCellValue(Enum.GetName(typeof(ItemStatus), item.ItemStatus));
cell.CellStyle = textStyle;
var cell2 = row.CreateCell(6);
cell2.SetCellValue((double)item.PricePaidBySeller);
cell2.CellStyle = currencyStyle;
cell = row.CreateCell(7);
cell.SetCellValue((double)item.TaxPaidBySeller);
cell.CellStyle = currencyStyle;
cell = row.CreateCell(8);
cell.SetCellValue((double)item.ShippingCostToSeller);
cell.CellStyle = currencyStyle;
cell = row.CreateCell(9);
cell.SetCellValue((double)item.ShippingCostToBuyer);
cell.CellStyle = currencyStyle;
cell = row.CreateCell(10);
cell.SetCellValue((double)item.Fees);
cell.CellStyle = currencyStyle;
cell = row.CreateCell(11);
cell.SetCellValue((double)item.OtherExpenses);
cell.CellStyle = currencyStyle;
cell = row.CreateCell(12);
cell.SetCellValue((double)item.ShippingPaidByBuyer);
cell.CellStyle = currencyStyle;
cell = row.CreateCell(13);
cell.SetCellValue((double)item.PricePaidByBuyer);
cell.CellStyle = currencyStyle;
cell = row.CreateCell(14);
cell.SetCellValue(item.TimeStampOrdered);
cell.CellStyle = dateStyle;
cell = row.CreateCell(15);
cell.SetCellValue(item.TimeStampReceived);
cell.CellStyle = dateStyle;
cell = row.CreateCell(16);
cell.SetCellValue(item.TimeStampSold);
cell.CellStyle = dateStyle;
}
}
excelFile.Write(stream, true);
stream.Position = 0;
string excelName = $"DatabaseDump-{DateTime.UtcNow:yyyy-MM-dd-hh:mm:ss:fffffff}.xlsx";
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName));
}
/// <summary>
/// 根据集合生成单个Excel
/// </summary>
/// <param name="List"></param>
/// <returns></returns>
private IWorkbook GenerateWorkBook(List <TModel> List)
{
IWorkbook book = new XSSFWorkbook();
ISheet sheet = book.CreateSheet();
IRow row = sheet.CreateRow(0);
//创建表头样式
ICellStyle headerStyle = book.CreateCellStyle();
headerStyle.FillBackgroundColor = ExportTitleBackColor == null ? HSSFColor.LightBlue.Index : ExportTitleBackColor.Value;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.FillForegroundColor = ExportTitleBackColor == null ? HSSFColor.LightBlue.Index : ExportTitleBackColor.Value;
IFont font = book.CreateFont();
font.FontName = "Calibri";
font.FontHeightInPoints = 12;
font.Color = ExportTitleFontColor == null ? HSSFColor.Black.Index : ExportTitleFontColor.Value;
headerStyle.SetFont(font);
//生成表头
MakeExcelHeader(sheet, GridHeaders, 0, 0, headerStyle);
//放入数据
var ColIndex = 0;
for (int i = 0; i < List.Count; i++)
{
ColIndex = 0;
var DR = sheet.CreateRow(i + 1);
foreach (var baseCol in GridHeaders)
{
//处理枚举变量的多语言
bool IsEmunBoolParp = false;
var proType = baseCol.FieldType;
if (proType.IsEnumOrNullableEnum())
{
IsEmunBoolParp = true;
}
foreach (var col in baseCol.BottomChildren)
{
//获取数据，并过滤特殊字符
string text = Regex.Replace(col.GetText(List[i]).ToString(), @"<[^>]*>", String.Empty);
//处理枚举变量的多语言
if (IsEmunBoolParp)
{
if (int.TryParse(text, out int enumvalue))
{
text = PropertyHelper.GetEnumDisplayName(proType, enumvalue);
}
}
//建立excel单元格
var cell = DR.CreateCell(ColIndex);
cell.SetCellValue(text);
ColIndex++;
}
}
}
return(book);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="path">当前应用程序的物理路径 Server.MapPath("~/");</param>
/// <param name="sheet">已封装好的Excel Sheet对象</param>
/// <returns>Excel文件名</returns>
public string Export(NPOISheetModel sheet, string path)
{
DirectoryInfo dinfo = new DirectoryInfo(path);
if (!dinfo.Exists)
{
dinfo.Create();
}
//内容行开始的索引
int rowIndes = 0;
int rowCount = sheet.TableTitle.Count - 1;
XSSFWorkbook wk = new XSSFWorkbook();
//IWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(sheet.ExcelTitle + "报表");
//创建一行，此行为第二行
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 0, rowCount));
IRow row = tb.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue(sheet.ExcelTitle);
IFont font = wk.CreateFont();
font.FontName = "微软雅黑";
font.Boldweight = (short)FontBoldWeight.Bold;
//font.Color = HSSFColor.OliveGreen.Black.Index;
font.FontHeightInPoints = 14;
ICellStyle cellstyle = wk.CreateCellStyle();
cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.SetFont(font);
//为标题添加样式
cell.CellStyle = cellstyle;
//添加统计
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, rowCount));
row = tb.CreateRow(3);
cell = row.CreateCell(0);
cell.SetCellValue("统计：共" + sheet.dt.Rows.Count.ToString() + "行");
//添加查询条件
rowIndes = 4;
foreach (KeyValuePair <string, string> item in sheet.TableSearch)
{
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndes, rowIndes, 0, rowCount));
row = tb.CreateRow(rowIndes);
cell = row.CreateCell(0);
cell.SetCellValue(item.Key + "：" + item.Value);
rowIndes++;
}
//填充标题
row = tb.CreateRow(rowIndes);
int y = 0;
foreach (KeyValuePair <string, string> item in sheet.TableTitle)
{
cell = row.CreateCell(y);
cell.SetCellValue(item.Key);
//单位格字体加粗
IFont fontTitle = wk.CreateFont();
fontTitle.Boldweight = (short)FontBoldWeight.Bold;
fontTitle.FontHeightInPoints = 12;
cellstyle = wk.CreateCellStyle();
cellstyle.SetFont(fontTitle);
cell.CellStyle = cellstyle;
//设定列宽度
if (item.Key == "序号")
{
tb.SetColumnWidth(y, 10 * 200);
}
else if (item.Key == "生产商" || item.Key == "URL")
{
tb.SetColumnWidth(y, 25 * 300);
}
else
{
tb.SetColumnWidth(y, 25 * 200);
}
y++;
}
rowIndes++;
//创建行/单元格，填充数据
for (int k = 0; k < sheet.dt.Rows.Count; k++)
{
row = tb.CreateRow(k + rowIndes);
y = 0;
foreach (KeyValuePair <string, string> item in sheet.TableTitle)
{
//在第k行中创建单元格
cell = row.CreateCell(y);
//循环往第二行的单元格中添加数据
cell.SetCellValue(FormatDataByKey(item.Value, sheet.dt.Rows[k][item.Value].ToString(), sheet, y));
y++;
}
}
//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
path += "/" + Guid.NewGuid() + ".xlsx";
MemoryStream stream = new MemoryStream();
wk.Write(stream);
var buf = stream.ToArray();
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length); //向打开的这个xls文件中写入mySheet表并保存。
}
return(path.Substring(path.IndexOf("UserData")));
}
/// <summary>
/// 导出数据至Excel
/// </summary>
/// <returns></returns>
public ActionResult ExportExcel(int packageId = 0)
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet();
var row = sheet.CreateRow(0);
var cell = null as ICell;
var cellNum = 0;
row.CreateCell(cellNum++).SetCellValue("PID");
row.CreateCell(cellNum++).SetCellValue("面数");
row.CreateCell(cellNum++).SetCellValue("活动价");
row.CreateCell(cellNum++).SetCellValue("权益名称");
row.CreateCell(cellNum++).SetCellValue("活动说明");
row.CreateCell(cellNum++).SetCellValue("活动图片");
cellNum = 0;
sheet.SetColumnWidth(cellNum++, 18 * 256);
sheet.SetColumnWidth(cellNum++, 14 * 256);
sheet.SetColumnWidth(cellNum++, 8 * 256);
sheet.SetColumnWidth(cellNum++, 18 * 256);
sheet.SetColumnWidth(cellNum++, 50 * 256);
sheet.SetColumnWidth(cellNum++, 50 * 256);
sheet.SetColumnWidth(cellNum++, 28 * 256);
var manager = new PaintDiscountConfigManager();
var packageName = string.Empty;
if (packageId > 0)
{
var package = manager.GetPaintDiscountPackage(packageId);
if (package == null)
{
return(Json(new { Status = false, Msg = "未知的喷漆打折价格体系" }, JsonRequestBehavior.AllowGet));
}
else
{
packageName = package.PackageName;
}
}
var result = manager.GetPaintDiscountDetailByPackageId(packageId);
if (result != null && result.Any())
{
int modelRowCount = 1;
foreach (var model in result)
{
int modelCol = 0;
var modelRow = sheet.CreateRow(modelRowCount);
modelRow.CreateCell(modelCol++).SetCellValue(model.ServicePid);
modelRow.CreateCell(modelCol++).SetCellValue(model.SurfaceCount);
modelRow.CreateCell(modelCol++).SetCellValue((double)model.ActivityPrice);
modelRow.CreateCell(modelCol++).SetCellValue(model.ActivityName);
modelRow.CreateCell(modelCol++).SetCellValue(model.ActivityExplain);
modelRow.CreateCell(modelCol++).SetCellValue(model.ActivityImage);
modelRowCount++;
}
}
var ms = new MemoryStream();
workbook.Write(ms);
return(File(ms.ToArray(), "application/x-xls",
$"喷漆打折价格体系{packageName}的服务价格配置 {DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒")}.xlsx"));
}
public void GenerarExcel()
{
var NombreExcel = "Producto - Sistemas de Ventas ";
// Recuperamos la data de las consulta DB
var data = (List <Annies.Entities.Producto>)Session["ReporteProducto"];
// Creación del libro excel xlsx.
var wb = new XSSFWorkbook();
// Creación del la hoja y se especifica un nombre
var fileName = WorkbookUtil.CreateSafeSheetName("Productos");
ISheet sheet = wb.CreateSheet(fileName);
// Contadores para filas y columnas.
int rownum = 0;
int cellnum = 0;
// Creacion del estilo de la letra para las cabeceras.
var fontCab = wb.CreateFont();
fontCab.FontHeightInPoints = 10;
fontCab.FontName = "Calibri";
fontCab.Boldweight = (short)FontBoldWeight.Bold;
fontCab.Color = HSSFColor.White.Index;
// Creacion del color del estilo.
var colorCab = new XSSFColor(new byte[] { 7, 105, 173 });
// Se crea el estilo y se agrega el font al estilo
var styleCab = (XSSFCellStyle)wb.CreateCellStyle();
styleCab.SetFont(fontCab);
styleCab.FillForegroundXSSFColor = colorCab;
styleCab.FillPattern = FillPattern.SolidForeground;
string[] Cabezeras =
{
"Código Producto", "Stock", "Codigo Almacén", "Marca", "Talla", "Talla Vendida", "Precio", "Estado", "Fecha"
};
// Se crea la primera fila para las cabceras.
IRow row = sheet.CreateRow(rownum++);
ICell cell;
foreach (var item in Cabezeras)
{
// Se crea celdas y se agrega las cabeceras
cell = row.CreateCell(cellnum++);
cell.SetCellValue(item);
cell.CellStyle = styleCab;
sheet.AutoSizeColumn(cellnum);
}
// Creacion del estilo de la letra para la data.
var fontBody = wb.CreateFont();
fontBody.FontHeightInPoints = 10;
fontBody.FontName = "Arial";
var styleBody = (XSSFCellStyle)wb.CreateCellStyle();
styleBody.SetFont(fontBody);
// Impresión de la data
foreach (var item in data)
{
cellnum = 0;
row = sheet.CreateRow(rownum++);
sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Cod_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Stock_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Codigo_Al, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Marca_Prod, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Talla_Prod, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Talla_Vendida_Prod, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Precio_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Estado_Prod == 1 ? "Activo" : "Inactivo".ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.FechaDesde.ToString().Substring(6, 2) + "/" + item.FechaDesde.ToString().Substring(4, 2) + "/" + item.FechaDesde.ToString().Substring(0, 4), styleBody); sheet.AutoSizeColumn(cellnum);
}
var nameFile = NombreExcel + DateTime.Now.ToString("dd_MM_yyyy HH:mm:ss") + ".xlsx";
Response.AddHeader("content-disposition", "attachment; filename=" + nameFile);
Response.ContentType = "application/octet-stream";
Stream outStream = Response.OutputStream;
wb.Write(outStream);
outStream.Close();
Response.End();
}
/// <summary>
/// 导出数据至EXCEL
/// </summary>
/// <param name="fileAddress"></param>
/// <param name="temp">MEASUREMENT_COLOR 表头导出记录</param>
/// <param name="tempdtl">MEASUREMENT 表体导出记录</param>
/// <returns></returns>
public bool ExportDtToExcel(string fileAddress, DataTable temp, DataTable tempdtl)
{
var result = true;
var sheetcount = 0; //记录所需的sheet页总数
var rownum = 1;
try
{
//声明一个WorkBook
var xssfWorkbook = new XSSFWorkbook();
//先执行MEASUREMENT_COLOR sheet页(注:1)先列表temp行数判断需拆分多少个sheet表进行填充; 以一个sheet表有9W行记录填充为基准)
sheetcount = temp.Rows.Count % 100000 == 0 ? temp.Rows.Count / 100000 : temp.Rows.Count / 100000 + 1;
//i为EXCEL的Sheet页数ID
for (var i = 1; i <= sheetcount; i++)
{
//创建sheet页
var sheet = xssfWorkbook.CreateSheet("MEASUREMENT_COLOR" + i);
//创建"标题行"
var row = sheet.CreateRow(0);
//创建"MEASUREMENT_COLOR"sheet页各列标题
for (var j = 0; j < temp.Columns.Count; j++)
{
//设置列宽度
sheet.SetColumnWidth(j, (int)((20 + 0.72) * 256));
//创建标题
switch (j)
{
#region SetCellValue
case 0:
row.CreateCell(j).SetCellValue("BMMEASUREMENTID");
break;
case 1:
row.CreateCell(j).SetCellValue("COLORCODE");
break;
case 2:
row.CreateCell(j).SetCellValue("FORMULAVERSIONDATE");
break;
case 3:
row.CreateCell(j).SetCellValue("DIFFUSECOARSENESS");
break;
case 4:
row.CreateCell(j).SetCellValue("CREATEDDATE");
break;
case 5:
row.CreateCell(j).SetCellValue("MEASUREMENTTIME");
break;
#endregion
}
}
//计算进行循环的起始行
var startrow = (i - 1) * 100000;
//计算进行循环的结束行
var endrow = i == sheetcount ? temp.Rows.Count : i * 100000;
//每一个sheet表显示90000行
for (var j = startrow; j < endrow; j++)
{
//创建行
row = sheet.CreateRow(rownum);
//循环获取DT内的列值记录
for (var k = 0; k < temp.Columns.Count; k++)
{
row.CreateCell(k).SetCellValue(Convert.ToString(temp.Rows[j][k]));
}
rownum++;
}
//当一个SHEET页填充完毕后,需将变量初始化
rownum = 1;
}
//创建"MEASUREMENT"
//先执行MEASUREMENT sheet页(注:1)先列表temp行数判断需拆分多少个sheet表进行填充;以一个sheet表有9W行记录填充为基准)
sheetcount = tempdtl.Rows.Count % 100000 == 0 ? tempdtl.Rows.Count / 100000 : tempdtl.Rows.Count / 100000 + 1;
//i为EXCEL的Sheet页数ID
for (var i = 1; i <= sheetcount; i++)
{
//创建sheet页
var sheet = xssfWorkbook.CreateSheet("MEASUREMENT" + i);
//创建"标题行"
var row = sheet.CreateRow(0);
//创建"MEASUREMENT"sheet页各列标题
for (var j = 0; j < tempdtl.Columns.Count; j++)
{
//设置列宽度
sheet.SetColumnWidth(j, (int)((20 + 0.72) * 256));
//创建标题
switch (j)
{
#region CellValue
case 0:
row.CreateCell(j).SetCellValue("BMAID");
break;
case 1:
row.CreateCell(j).SetCellValue("ANGLE");
break;
case 2:
row.CreateCell(j).SetCellValue("BMMEASUREMENTID");
break;
case 3:
row.CreateCell(j).SetCellValue("L");
break;
case 4:
row.CreateCell(j).SetCellValue("A");
break;
case 5:
row.CreateCell(j).SetCellValue("B");
break;
case 6:
row.CreateCell(j).SetCellValue("C");
break;
case 7:
row.CreateCell(j).SetCellValue("H");
break;
case 8:
row.CreateCell(j).SetCellValue("BMA_R400");
break;
case 9:
row.CreateCell(j).SetCellValue("BMA_R410");
break;
case 10:
row.CreateCell(j).SetCellValue("BMA_R420");
break;
case 11:
row.CreateCell(j).SetCellValue("BMA_R430");
break;
case 12:
row.CreateCell(j).SetCellValue("BMA_R440");
break;
case 13:
row.CreateCell(j).SetCellValue("BMA_R450");
break;
case 14:
row.CreateCell(j).SetCellValue("BMA_R460");
break;
case 15:
row.CreateCell(j).SetCellValue("BMA_R470");
break;
case 16:
row.CreateCell(j).SetCellValue("BMA_R480");
break;
case 17:
row.CreateCell(j).SetCellValue("BMA_R490");
break;
case 18:
row.CreateCell(j).SetCellValue("BMA_R500");
break;
case 19:
row.CreateCell(j).SetCellValue("BMA_R510");
break;
case 20:
row.CreateCell(j).SetCellValue("BMA_R520");
break;
case 21:
row.CreateCell(j).SetCellValue("BMA_R530");
break;
case 22:
row.CreateCell(j).SetCellValue("BMA_R540");
break;
case 23:
row.CreateCell(j).SetCellValue("BMA_R550");
break;
case 24:
row.CreateCell(j).SetCellValue("BMA_R560");
break;
case 25:
row.CreateCell(j).SetCellValue("BMA_R570");
break;
case 26:
row.CreateCell(j).SetCellValue("BMA_R580");
break;
case 27:
row.CreateCell(j).SetCellValue("BMA_R590");
break;
case 28:
row.CreateCell(j).SetCellValue("BMA_R600");
break;
case 29:
row.CreateCell(j).SetCellValue("BMA_R610");
break;
case 30:
row.CreateCell(j).SetCellValue("BMA_R620");
break;
case 31:
row.CreateCell(j).SetCellValue("BMA_R630");
break;
case 32:
row.CreateCell(j).SetCellValue("BMA_R640");
break;
case 33:
row.CreateCell(j).SetCellValue("BMA_R650");
break;
case 34:
row.CreateCell(j).SetCellValue("BMA_R660");
break;
case 35:
row.CreateCell(j).SetCellValue("BMA_R670");
break;
case 36:
row.CreateCell(j).SetCellValue("BMA_R680");
break;
case 37:
row.CreateCell(j).SetCellValue("BMA_R690");
break;
case 38:
row.CreateCell(j).SetCellValue("BMA_R700");
break;
#endregion
}
}
//计算进行循环的起始行
var startrow = (i - 1) * 100000;
//计算进行循环的结束行
var endrow = i == sheetcount ? tempdtl.Rows.Count : i * 100000;
//获取DT内的行记录步骤(重)
//每一个sheet表显示100000行
for (var j = startrow; j < endrow; j++)
{
//创建行
row = sheet.CreateRow(rownum);
//循环获取DT内的列值记录
for (var k = 0; k < tempdtl.Columns.Count; k++)
{
row.CreateCell(k).SetCellValue(Convert.ToDouble(tempdtl.Rows[j][k]));
}
rownum++;
}
//当一个SHEET页填充完毕后,需将变量初始化
rownum = 1;
}
////写入数据
var file = new FileStream(fileAddress, FileMode.Create);
xssfWorkbook.Write(file);
file.Close();
}
catch (Exception)
{
result = false;
}
return(result);
}
public ActionResult Details()
{
string sWebRootFolder = HttpRuntime.AppDomainAppPath;
string sFileName = @"Test.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Url.Scheme, Request.Url.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Test");
IRow row = excelSheet.CreateRow(0);
int x = 0;
row.CreateCell(x++).SetCellValue("Employee ID");
row.CreateCell(x++).SetCellValue("Last Name");
row.CreateCell(x++).SetCellValue("First Name");
row.CreateCell(x++).SetCellValue("Date");
row.CreateCell(x++).SetCellValue("Clock In Time");
row.CreateCell(x++).SetCellValue("Clock Out Time");
row.CreateCell(x++).SetCellValue("Hours Worked");
row.CreateCell(x++).SetCellValue("Time Type");
row.CreateCell(x++).SetCellValue("Overtime Hours");
row.CreateCell(x++).SetCellValue("PTO Earned");
row.CreateCell(x++).SetCellValue("");
row.CreateCell(x++).SetCellValue("");
int j = 1;
int timeID = (int)TempData.Peek("id");
var time = (db.TIME_SHEET_ENTRY.Where(i => i.time_sheet == timeID)).ToList();
var empID = db.TIME_SHEET.Where(i => i.timesheetID == timeID).Select(i => i.employee).FirstOrDefault();
var fname = db.USERs.Where(i => i.userID == empID).Select(i => i.fname).FirstOrDefault();
var lname = db.USERs.Where(i => i.userID == empID).Select(i => i.lname).FirstOrDefault();
foreach (var entry in time)
{
row = excelSheet.CreateRow(j);
int i = 0;
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.employee));
row.CreateCell(i++).SetCellValue(Convert.ToString(lname));
row.CreateCell(i++).SetCellValue(Convert.ToString(fname));
if (entry.date.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.date));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.date));
}
if (entry.clock_in_time.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.clock_in_time));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.clock_in_time));
}
if (entry.clock_out_time.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.clock_out_time));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.clock_out_time));
}
if (entry.hours_worked.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.hours_worked));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.hours_worked));
}
row.CreateCell(i++).SetCellValue(entry.time_type);
if (entry.overtime_hours_worked.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.overtime_hours_worked));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.overtime_hours_worked));
}
if (entry.pto_earned.HasValue)
{
row.CreateCell(i++).SetCellValue(Convert.ToString(entry.pto_earned));
//System.Diagnostics.Debug.WriteLine(Convert.ToString(entry.pto_earned));
}
j++;
}
workbook.Write(fs);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
stream.CopyTo(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
public static byte[] ExportToByte(DataTable dt, ExcelVersion version = ExcelVersion.V2007)
{
IWorkbook workbook;
ICellStyle cellStyle = null;
IFont font = null;
switch (version)
{
case ExcelVersion.V2007:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
case ExcelVersion.V2003:
workbook = new HSSFWorkbook();
cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
font = (HSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
default:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
}
int count = dt.Rows.Count;
int sheetCount = 1;
if (count >= Max)
{
sheetCount = GetLen(count);
}
for (int i = 0; i < sheetCount; i++)
{
var sheetname = "sheet" + (i + 1);
ISheet sheet = workbook.CreateSheet(sheetname);
IRow row = sheet.CreateRow(0);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = cellStyle;
cell.SetCellValue(dt.Columns[j].ColumnName);
}
var newDt = dt.Clone();
dt.Rows.Cast <DataRow>().Skip(Max * i).Take(Max).ToList().ForEach(r => newDt.ImportRow(r));
//数据
for (int k = 0; k < newDt.Rows.Count; k++)
{
IRow row1 = sheet.CreateRow(k + 1);
for (int m = 0; m < newDt.Columns.Count; m++)
{
ICell cell = row1.CreateCell(m);
cell.CellStyle = cellStyle;
cell.SetCellValue(newDt.Rows[k][m]?.ToString());
}
}
AutoSizeColumns(sheet);
}
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buffer = stream.ToArray();
return(buffer);
}
public void Run()
{
IWorkbook workbook = new XSSFWorkbook();
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
// font Test용 Sheet생성
ISheet sheet1 = workbook.CreateSheet("Font_Test Sheet");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "굴림체";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
/*
* BorderStryle
* */
ISheet sheet3 = workbook.CreateSheet("BorderStype");
IRow row = sheet3.CreateRow(1);
// Create a cell and put a value in it.
cell = row.CreateCell(1);
cell.SetCellValue(4);
// Style the cell with borders all around.
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = IndexedColors.Black.Index;
style.BorderLeft = BorderStyle.DashDotDot;
style.LeftBorderColor = IndexedColors.Green.Index;
style.BorderRight = BorderStyle.Hair;
style.RightBorderColor = IndexedColors.Blue.Index;
style.BorderTop = BorderStyle.MediumDashed;
style.TopBorderColor = IndexedColors.Orange.Index;
//create border diagonal
style.BorderDiagonalLineStyle = BorderStyle.Medium; //this property must be set before BorderDiagonal and BorderDiagonalColor
style.BorderDiagonal = BorderDiagonal.Forward;
style.BorderDiagonalColor = IndexedColors.Gold.Index;
cell.CellStyle = style;
// Create a cell and put a value in it.
cell2 = row.CreateCell(2);
cell2.SetCellValue(5);
style2 = workbook.CreateCellStyle();
style2.BorderDiagonalLineStyle = BorderStyle.Medium;
style2.BorderDiagonal = BorderDiagonal.Backward;
style2.BorderDiagonalColor = IndexedColors.Red.Index;
cell2.CellStyle = style2;
using (FileStream fs = File.Create(@"C:\00.Dev\temp\HyprtLink_Font.xlsx"))
{
workbook.Write(fs);
}
}
public static void ExportToExcel(DataSet ds, string ExcelFilePath = null)
{
try
{
XSSFWorkbook hssfwb;
hssfwb = new XSSFWorkbook();
XSSFSheet sh;
int sheetcount = 0;
XSSFFont _style = (XSSFFont)hssfwb.CreateFont();
_style.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
_style.Boldweight = 2;
foreach (DataTable table in ds.Tables)
{
sh = (XSSFSheet)hssfwb.CreateSheet(ds.Tables[sheetcount].TableName);
for (int col = 1; col < table.Columns.Count + 1; col++)
{
if (col == 1)
{
sh.CreateRow(0);
}
sh.GetRow(0)
.CreateCell(col - 1);
sh.GetRow(0)
.GetCell(col - 1)
.SetCellValue(table.Columns[col - 1].ColumnName);
}
for (int i = 0; i < table.Rows.Count; i++)
{
var r = sh.CreateRow(i + 1);
for (int j = 0; j < table.Columns.Count; j++)
{
sh.GetRow(i + 1)
.CreateCell(j);
sh.GetRow(i + 1)
.GetCell(j)
.SetCellValue(table.Rows[i].ItemArray[j].ToString());
sh.GetRow(i + 1)
.GetCell(j)
.SetCellType(CellType.String);
//if (coloringrows != null && coloringrows.Count > 0 && coloringrows.Contains(i))
//{
// sh.GetRow(i + 1)
// .GetCell(j)
// .CellStyle.SetFont(_style);
// coloringrows.Remove(i);
//}
}
}
sheetcount++;
}
using (FileStream file = new FileStream(ExcelFilePath, FileMode.Create, FileAccess.Write))
{
hssfwb.Write(file);
file.Close();
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public void TestGetOrCreateColumn()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet 1");
ColumnHelper columnHelper = sheet.GetColumnHelper();
// Check POI 0 based, OOXML 1 based
CT_Col col = columnHelper.GetOrCreateColumn1Based(3, false);
Assert.IsNotNull(col);
Assert.IsNull(columnHelper.GetColumn(1, false));
Assert.IsNotNull(columnHelper.GetColumn(2, false));
Assert.IsNotNull(columnHelper.GetColumn1Based(3, false));
Assert.IsNull(columnHelper.GetColumn(3, false));
CT_Col col2 = columnHelper.GetOrCreateColumn1Based(30, false);
Assert.IsNotNull(col2);
Assert.IsNull(columnHelper.GetColumn(28, false));
Assert.IsNotNull(columnHelper.GetColumn(29, false));
Assert.IsNotNull(columnHelper.GetColumn1Based(30, false));
Assert.IsNull(columnHelper.GetColumn(30, false));
}
/// <summary>
/// return File(buffer, "application/ms-excel", "list.xlsx");
/// </summary>
/// <param name="dt"></param>
/// <param name="version"></param>
/// <returns></returns>
public static byte[] Export(DataTable dt, ExcelVersion version = ExcelVersion.V2007)
{
IWorkbook workbook;
ICellStyle cellStyle = null;
IFont font = null;
switch (version)
{
case ExcelVersion.V2007:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
case ExcelVersion.V2003:
workbook = new HSSFWorkbook();
cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
font = (HSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
default:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
}
string sheetname = "sheet1";
ISheet sheet = workbook.CreateSheet(sheetname);
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.CellStyle = cellStyle;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.CellStyle = cellStyle;
cell.SetCellValue(dt.Rows[i][j]?.ToString());
}
}
AutoSizeColumns(sheet);
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buffer = stream.ToArray();
return(buffer);
}
public async Task ExportProtocol(int ProtocolId)
{
Protocol protocol = _repoWrapper.Protocol.GetById(ProtocolId);
if (protocol == null)
{
return;
}
string fileName = @"{0}-{1}.xlsx";
fileName = string.Format(fileName, protocol.Data.ToString("yyyy-MM-dd"), protocol.ProtocolId);
IWorkbook workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet();
IRow rowHeader = excelSheet.CreateRow(0);
IRow rowData = excelSheet.CreateRow(1);
rowHeader.CreateCell(0).SetCellValue("Протокол №");
rowData.CreateCell(0).SetCellValue(protocol.ProtocolId.ToString());
rowHeader.CreateCell(1).SetCellValue("Дата");
rowData.CreateCell(1).SetCellValue(protocol.Data.ToString("dd/MM/yyyy"));
rowHeader.CreateCell(2).SetCellValue("Служебен №");
rowData.CreateCell(2).SetCellValue(protocol.SlujNomer.ToString());
rowHeader.CreateCell(3).SetCellValue("Рег. №");
rowData.CreateCell(3).SetCellValue(protocol.RegNomer);
rowHeader.CreateCell(4).SetCellValue("Пробег");
rowData.CreateCell(4).SetCellValue(protocol.Probeg.ToString());
rowHeader.CreateCell(5).SetCellValue("Водач");
rowData.CreateCell(5).SetCellValue(protocol.Vodach);
rowHeader.CreateCell(6).SetCellValue("ВЪНШЕН ВИД ХИГИЕНА");
rowData.CreateCell(6).SetCellValue(protocol.VVHigiena);
rowHeader.CreateCell(7).SetCellValue("ВЪНШЕН ВИД ХИГИЕНА");
rowData.CreateCell(7).SetCellValue(protocol.VVHigienaDaNe);
rowHeader.CreateCell(8).SetCellValue("ВЪНШЕН ВИД БРОНИ");
rowData.CreateCell(8).SetCellValue(protocol.VVBroni);
rowHeader.CreateCell(9).SetCellValue("ВЪНШЕН ВИД БРОНИ");
rowData.CreateCell(9).SetCellValue(protocol.VVBroniDaNe);
rowHeader.CreateCell(10).SetCellValue("ВЪНШЕН ВИД КАЛНИЦИ");
rowData.CreateCell(10).SetCellValue(protocol.VVKalnici);
rowHeader.CreateCell(11).SetCellValue("ВЪНШЕН ВИД КАЛНИЦИ");
rowData.CreateCell(11).SetCellValue(protocol.VVKalniciDaNe);
rowHeader.CreateCell(12).SetCellValue("ВЪНШЕН ВИД ВРАТИ");
rowData.CreateCell(12).SetCellValue(protocol.VVVrati);
rowHeader.CreateCell(13).SetCellValue("ВЪНШЕН ВИД ВРАТИ");
rowData.CreateCell(13).SetCellValue(protocol.VVVratiDaNe);
rowHeader.CreateCell(14).SetCellValue("ВЪНШЕН ВИД СТЪКЛА");
rowData.CreateCell(14).SetCellValue(protocol.VVStykla);
rowHeader.CreateCell(15).SetCellValue("ВЪНШЕН ВИД СТЪКЛА");
rowData.CreateCell(15).SetCellValue(protocol.VVStyklaDaNe);
rowHeader.CreateCell(16).SetCellValue("ВЪНШЕН ВИД ДРУГИ");
rowData.CreateCell(16).SetCellValue(protocol.VVDrugi);
rowHeader.CreateCell(17).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ДВИГАТЕЛ");
rowData.CreateCell(17).SetCellValue(protocol.ZADvigatel);
rowHeader.CreateCell(18).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ДВИГАТЕЛ");
rowData.CreateCell(18).SetCellValue(protocol.ZADvigatelDaNe);
rowHeader.CreateCell(19).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ РЕМЪЦИ");
rowData.CreateCell(19).SetCellValue(protocol.ZaRemyci);
rowHeader.CreateCell(20).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ РЕМЪЦИ");
rowData.CreateCell(20).SetCellValue(protocol.ZaRemyciDaNe);
rowHeader.CreateCell(21).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ОХЛАЖДАЩА УРЕДБА");
rowData.CreateCell(21).SetCellValue(protocol.ZAOhlajdashtaUredba);
rowHeader.CreateCell(22).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ОХЛАЖДАЩА УРЕДБА");
rowData.CreateCell(22).SetCellValue(protocol.ZAOhlajdashtaUredbaDaNe);
rowHeader.CreateCell(23).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ГОРИВНА УРЕДБА");
rowData.CreateCell(23).SetCellValue(protocol.ZAGorivnaUredba);
rowHeader.CreateCell(24).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ГОРИВНА УРЕДБА");
rowData.CreateCell(24).SetCellValue(protocol.ZAGorivnaUredbaDaNe);
rowHeader.CreateCell(25).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ИЗПУСКАТЕЛНА УРЕДБА");
rowData.CreateCell(25).SetCellValue(protocol.ZAIzpuskatelnaUredba);
rowHeader.CreateCell(26).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ИЗПУСКАТЕЛНА УРЕДБА");
rowData.CreateCell(26).SetCellValue(protocol.ZAIzpuskatelnaUredbaDaNe);
rowHeader.CreateCell(27).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ МАЗИЛНА УРЕДБА");
rowData.CreateCell(27).SetCellValue(protocol.ZAMazilnaUredba);
rowHeader.CreateCell(28).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ МАЗИЛНА УРЕДБА");
rowData.CreateCell(28).SetCellValue(protocol.ZAMazilnaUredbaDaNe);
rowHeader.CreateCell(29).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ДРУГИ");
rowData.CreateCell(29).SetCellValue(protocol.ZADrugi);
rowHeader.CreateCell(30).SetCellValue("ТРАНСМИСИЯ СЪЕДИНИТЕЛ");
rowData.CreateCell(30).SetCellValue(protocol.TMSyedinitel);
rowHeader.CreateCell(31).SetCellValue("ТРАНСМИСИЯ СЪЕДИНИТЕЛ");
rowData.CreateCell(31).SetCellValue(protocol.TMSyedinitelDaNe);
rowHeader.CreateCell(32).SetCellValue("ТРАНСМИСИЯ СКОРОСТНА КУТИЯ И ДИФЕРЕНЦИАЛ");
rowData.CreateCell(32).SetCellValue(protocol.TMSkorostnaKutiqIDiferencial);
rowHeader.CreateCell(33).SetCellValue("ТРАНСМИСИЯ СКОРОСТНА КУТИЯ И ДИФЕРЕНЦИАЛ");
rowData.CreateCell(33).SetCellValue(protocol.TMSkorostnaKutiqIDiferencialDaNe);
rowHeader.CreateCell(34).SetCellValue("ТРАНСМИСИЯ ДРУГИ");
rowData.CreateCell(34).SetCellValue(protocol.TMDrugi);
rowHeader.CreateCell(35).SetCellValue("ХОДОВА ЧАСТ КОЛЕЛА");
rowData.CreateCell(35).SetCellValue(protocol.HCHodoviKolela);
rowHeader.CreateCell(36).SetCellValue("ХОДОВА ЧАСТ КОЛЕЛА");
rowData.CreateCell(36).SetCellValue(protocol.HCHodoviKolelaDaNe);
rowHeader.CreateCell(37).SetCellValue("ХОДОВА ЧАСТ ТАМПОНИ МФ");
rowData.CreateCell(37).SetCellValue(protocol.HCTamponiMF);
rowHeader.CreateCell(38).SetCellValue("ХОДОВА ЧАСТ ТАМПОНИ МФ");
rowData.CreateCell(38).SetCellValue(protocol.HCTamponiMFDaNe);
rowHeader.CreateCell(39).SetCellValue("ХОДОВА ЧАСТ АМОРТИЬОРИ ПРЕДНИ");
rowData.CreateCell(39).SetCellValue(protocol.HCAmortisioriPredni);
rowHeader.CreateCell(40).SetCellValue("ХОДОВА ЧАСТ АМОРТИЬОРИ ПРЕДНИ");
rowData.CreateCell(40).SetCellValue(protocol.HCAmortisioriPredniDaNe);
rowHeader.CreateCell(41).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ПРЕДНИ");
rowData.CreateCell(41).SetCellValue(protocol.HCNosachiPredni);
rowHeader.CreateCell(42).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ПРЕДНИ");
rowData.CreateCell(42).SetCellValue(protocol.HCNosachiPredniDaNe);
rowHeader.CreateCell(43).SetCellValue("ХОДОВА ЧАСТ ШАРНИРНИ БОЛТОВЕ");
rowData.CreateCell(43).SetCellValue(protocol.HCSharnirniBoltove);
rowHeader.CreateCell(44).SetCellValue("ХОДОВА ЧАСТ ШАРНИРНИ БОЛТОВЕ");
rowData.CreateCell(44).SetCellValue(protocol.HCSharnirniBoltoveDaNe);
rowHeader.CreateCell(45).SetCellValue("ХОДОВА ЧАСТ КАРЕТА");
rowData.CreateCell(45).SetCellValue(protocol.HCKareta);
rowHeader.CreateCell(46).SetCellValue("ХОДОВА ЧАСТ КАРЕТА");
rowData.CreateCell(46).SetCellValue(protocol.HCKaretaDaNe);
rowHeader.CreateCell(47).SetCellValue("ХОДОВА ЧАСТ МАНШОНИ");
rowData.CreateCell(47).SetCellValue(protocol.HCManshoni);
rowHeader.CreateCell(48).SetCellValue("ХОДОВА ЧАСТ МАНШОНИ");
rowData.CreateCell(48).SetCellValue(protocol.HCManshoniDaNe);
rowHeader.CreateCell(49).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ЗАДНИ");
rowData.CreateCell(49).SetCellValue(protocol.HCNosachiZadni);
rowHeader.CreateCell(50).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ЗАДНИ");
rowData.CreateCell(50).SetCellValue(protocol.HCNosachiZadniDaNe);
rowHeader.CreateCell(51).SetCellValue("ХОДОВА ЧАСТ АМОРТИСЬОРИ ЗАДНИ");
rowData.CreateCell(51).SetCellValue(protocol.HCAmortisioriZadni);
rowHeader.CreateCell(52).SetCellValue("ХОДОВА ЧАСТ АМОРТИСЬОРИ ЗАДНИ");
rowData.CreateCell(52).SetCellValue(protocol.HCAmortisioriZadniDaNe);
rowHeader.CreateCell(53).SetCellValue("ХОДОВА ЧАСТ ЛАГЕРИ");
rowHeader.CreateCell(53).SetCellValue(protocol.HCLageri);
rowHeader.CreateCell(54).SetCellValue("ХОДОВА ЧАСТ ЛАГЕРИ");
rowData.CreateCell(54).SetCellValue(protocol.HCLageriDaNe);
rowHeader.CreateCell(55).SetCellValue("ХОДОВА ЧАСТ СПИРАЧНА УРЕДБА");
rowData.CreateCell(55).SetCellValue(protocol.HCSpirachnaUredba);
rowHeader.CreateCell(56).SetCellValue("ХОДОВА ЧАСТ СПИРАЧНА УРЕДБА");
rowData.CreateCell(56).SetCellValue(protocol.HCSpirachnaUredbaDaNe);
rowHeader.CreateCell(57).SetCellValue("ХОДОВА ЧАСТ СТАБ. ЩАНГИ И ТАМПОНИ");
rowData.CreateCell(57).SetCellValue(protocol.HCStabilizirashtiShtangiITamponi);
rowHeader.CreateCell(58).SetCellValue("ХОДОВА ЧАСТ СТАБ. ЩАНГИ И ТАМПОНИ");
rowData.CreateCell(58).SetCellValue(protocol.HCStabilizirashtiShtangiITamponiDaNe);
rowHeader.CreateCell(59).SetCellValue("ХОДОВА ЧАСТ КОРМИЛНО УПРАВЛЕНИЕ");
rowData.CreateCell(59).SetCellValue(protocol.HCKormilnoUpravlenie);
rowHeader.CreateCell(60).SetCellValue("ХОДОВА ЧАСТ КОРМИЛНО УПРАВЛЕНИЕ");
rowData.CreateCell(60).SetCellValue(protocol.HCKormilnoUpravlenieDaNe);
rowHeader.CreateCell(61).SetCellValue("ХОДОВА ЧАСТ ГЕОМЕТРИЯ ПРЕДЕН И ЗАДЕН МОСТ");
rowData.CreateCell(61).SetCellValue(protocol.HCGeometriaPredenIZadenMost);
rowHeader.CreateCell(62).SetCellValue("ХОДОВА ЧАСТ ГЕОМЕТРИЯ ПРЕДЕН И ЗАДЕН МОСТ");
rowData.CreateCell(62).SetCellValue(protocol.HCGeometriaPredenIZadenMostDaNe);
rowHeader.CreateCell(63).SetCellValue("ХОДОВА ЧАСТ БЕАЛЕТИ");
rowData.CreateCell(63).SetCellValue(protocol.HCBealeti);
rowHeader.CreateCell(64).SetCellValue("ХОДОВА ЧАСТ БЕАЛЕТИ");
rowData.CreateCell(64).SetCellValue(protocol.HCBealetiDaNe);
rowHeader.CreateCell(65).SetCellValue("ХОДОВА ЧАСТ ДРУГИ");
rowData.CreateCell(65).SetCellValue(protocol.HCDrugi);
rowHeader.CreateCell(66).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА КЛИМАТИК");
rowData.CreateCell(66).SetCellValue(protocol.ESKlimatik);
rowHeader.CreateCell(67).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА КЛИМАТИК");
rowData.CreateCell(67).SetCellValue(protocol.ESKlimatikDaNe);
rowHeader.CreateCell(68).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АКУМУЛАТОР");
rowData.CreateCell(68).SetCellValue(protocol.ESAkumulator);
rowHeader.CreateCell(69).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АКУМУЛАТОР");
rowData.CreateCell(69).SetCellValue(protocol.ESAkumulatorDaNe);
rowHeader.CreateCell(70).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СТАРТЕР И ГЕНЕРАТОР");
rowData.CreateCell(70).SetCellValue(protocol.ESStarterIGenerator);
rowHeader.CreateCell(69).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СТАРТЕР И ГЕНЕРАТОР");
rowData.CreateCell(69).SetCellValue(protocol.ESStarterIGeneratorDaNe);
rowHeader.CreateCell(70).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АРМАТУРНО ТАБЛО");
rowData.CreateCell(70).SetCellValue(protocol.ESArmaturnoTablo);
rowHeader.CreateCell(71).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АРМАТУРНО ТАБЛО");
rowData.CreateCell(71).SetCellValue(protocol.ESArmaturnoTabloDaNe);
rowHeader.CreateCell(72).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА ЧИСТАЧКИ");
rowData.CreateCell(72).SetCellValue(protocol.ESChistachki);
rowHeader.CreateCell(73).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА ЧИСТАЧКИ");
rowData.CreateCell(73).SetCellValue(protocol.ESChistachkiDaNe);
rowHeader.CreateCell(74).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СВЕТЛИНИ");
rowData.CreateCell(74).SetCellValue(protocol.ESSvetlini);
rowHeader.CreateCell(75).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СВЕТЛИНИ");
rowData.CreateCell(75).SetCellValue(protocol.ESSvetliniDaNe);
rowHeader.CreateCell(76).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА ДРУГИ");
rowData.CreateCell(76).SetCellValue(protocol.ESDrugi);
rowHeader.CreateCell(77).SetCellValue("ДРУГИ РЕКЛАМНА ТАБЕЛА");
rowData.CreateCell(77).SetCellValue(protocol.DRReklamnaTabela);
rowHeader.CreateCell(78).SetCellValue("ДРУГИ ВАЛИДНОСТ НА УТГ:");
rowData.CreateCell(78).SetCellValue(protocol.DRValidnostUTG);
rowHeader.CreateCell(79).SetCellValue("ИЗПРАВЕН");
rowData.CreateCell(79).SetCellValue(protocol.AvtomobilatEIzpraven ? "ДА" : "");
rowHeader.CreateCell(80).SetCellValue("НЕ ИЗПРАВЕН");
rowData.CreateCell(80).SetCellValue(protocol.AvtomobilatENeIzpraven ? "ДА" : "");
rowHeader.CreateCell(81).SetCellValue("МЕХАНИК");
rowData.CreateCell(81).SetCellValue(protocol.Mehanik);
rowHeader.CreateCell(82).SetCellValue("Вложени части");
rowData.CreateCell(82).SetCellValue(protocol.VlojeniChasti);
workbook.WriteExcelToResponse(HttpContext, fileName);
}
public static MemoryStream ExportDataTableToExcel(DataTable sourceTable, ExcelVersion version = ExcelVersion.V2007)
{
IWorkbook workbook;
ICellStyle cellStyle = null;
IFont font = null;
switch (version)
{
case ExcelVersion.V2007:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
case ExcelVersion.V2003:
workbook = new HSSFWorkbook();
cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
font = (HSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
default:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
}
int dtRowsCount = sourceTable.Rows.Count;
int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / Max));
int SheetNum = 1;
int rowIndex = 1;
int tempIndex = 1; //标示
ISheet sheet = workbook.CreateSheet("sheet" + SheetNum);
for (int i = 0; i < dtRowsCount; i++)
{
if (i == 0 || tempIndex == 1)
{
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
}
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString());
}
if (tempIndex == Max)
{
SheetNum++;
sheet = workbook.CreateSheet("sheet" + SheetNum);//
tempIndex = 0;
}
rowIndex++;
tempIndex++;
AutoSizeColumns(sheet);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
// headerRow = null;
workbook = null;
return(ms);
}
public static byte[] Export <T>(List <T> list, ExcelVersion version = ExcelVersion.V2007)
{
if (list.IsNullLt())
{
return(null);
}
string sheetname = "sheet1";
IWorkbook workbook;
ICellStyle cellStyle = null;
IFont font = null;
switch (version)
{
case ExcelVersion.V2007:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
case ExcelVersion.V2003:
workbook = new HSSFWorkbook();
cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
font = (HSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
default:
workbook = new XSSFWorkbook();
cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.FontName = "宋体";
cellStyle.SetFont(font);
break;
}
int count = list.Count;
int sheetCount = 1;
if (count >= Max)
{
sheetCount = GetLen(count);
}
for (int i = 0; i < sheetCount; i++)
{
sheetname = "sheet" + (i + 1);
ISheet sheet = workbook.CreateSheet(sheetname);
IRow row = sheet.CreateRow(0);
Type entityType = list[0].GetType();
PropertyInfo[] entityProperties = entityType.GetProperties();
for (int j = 0; j < entityProperties.Length; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = cellStyle;
cell.SetCellValue(entityProperties[j].Name);
}
var maxList = list.Skip(Max * i).Take(Max).ToList();
if (i == sheetCount - 1)
{
maxList.Take((count - i * Max)).ToList();
}
else
{
maxList.Take(Max).ToList();
}
//数据
for (int k = 0; k < maxList.Count; k++)
{
IRow row1 = sheet.CreateRow(k + 1);
var properties = maxList[k].GetType().GetProperties();
for (int m = 0; m < properties.Length; m++)
{
ICell cell = row1.CreateCell(m);
cell.CellStyle = cellStyle;
var id = properties[m].GetValue(maxList[k])?.ToString();
cell.SetCellValue(id);
}
}
AutoSizeColumns(sheet);
}
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buffer = stream.ToArray();
return(buffer);
}
public async Task <IActionResult> Export()
{
var excelPath = @"C:\Users\laishilin\Desktop\test.xlsx";
using FileStream fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write);
IWorkbook workbook = new XSSFWorkbook();
using var conn = _db.GetConnection();
var result = await conn.QueryAsync <EbookInfo>("SELECT * FROM ebook_info WHERE type = @Type", new { Type = BookType.Web });
// var models = result.ListMapTo<EbookInfo, BookModel>();
// ISheet sheet1 = workbook.CreateSheet("Test");
// sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
// var rowIndex = 0;
// IRow row = sheet1.CreateRow(rowIndex);
// row.Height = 30 * 30;
// row.CreateCell(0).SetCellValue("this is content");
// sheet1.AutoSizeColumn(0);
// rowIndex++;
var sheet = workbook.CreateSheet("book");
string[] titles = { "id", "number", "name", "type", "author", "publish", "publish_date", "created_on", "created_by",
"price", "score", "url", "discount", "is_discount", "modify_by", "modified_on", "download_times",
"description", "is_deleted", "actul_price" };
ICell cell;
IRow row;
row = sheet.CreateRow(0);
// bg-color:grey_25_percent;text-align:center;font-weight:bold;border-type:thin;
var cellStyle = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.IsBold = true;
// font.FontName = "宋体";
// font.FontHeightInPoints = 12;
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
// cellStyle.FillForegroundColor = HSSFColor.Grey50Percent.Index;
// cellStyle.FillPattern = FillPattern.SolidForeground;
// cellStyle.BottomBorderColor = HSSFColor.Black.Index;
// cellStyle.LeftBorderColor = HSSFColor.Black.Index;
// cellStyle.RightBorderColor = HSSFColor.Black.Index;
// cellStyle.TopBorderColor = HSSFColor.Black.Index;
// cellStyle.BorderBottom = BorderStyle.Double;
// cellStyle.BorderRight = BorderStyle.Double;
// cellStyle.BorderLeft = BorderStyle.Double;
// cellStyle.BorderTop = BorderStyle.Double;
cellStyle.SetFont(font);
for (int i = 0; i < titles.Length; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = cellStyle;
cell.SetCellValue(titles[i]);
}
int indexRow = 1;
foreach (var item in result)
{
row = sheet.CreateRow(indexRow);
var props = item.GetType().GetProperties();
int indexCol = 0;
foreach (var item2 in props)
{
cell = row.CreateCell(indexCol);
var o = item2.GetValue(item);
if (o == null)
{
cell.SetCellValue(string.Empty);
}
else
{
cell.SetCellValue(o.ToString());
}
indexCol++;
}
indexRow++;
}
//设置自适应宽度
for (int columnNum = 0; columnNum < titles.Length; columnNum++)
{
sheet.AutoSizeColumn(columnNum);
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
var num = sheet.LastRowNum;
for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow = sheet.GetRow(rowNum);
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 240);
}
workbook.Write(fs);
return(Ok());
// return File(System.IO.File.OpenRead(@"C:\Users\laishilin\Desktop\test.xlsx"), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "导出数据.xlsx");
}
