public void AjustarTamanioColumnasHojaActual()
{
for (int i = 0; i <= 20; i++)
{
currentSheet.AutoSizeColumn(i);
}
}
public static void SaveRToXls(Stream filegrp, string fileidx, string inifilepath, string xlsFile)
{
HSSFWorkbook newBook = new HSSFWorkbook();
List <object> rows = new List <object>();
List <RdataType> datalist = ReadRanger(filegrp, fileidx, inifilepath);
for (int k = 0; k < datalist.Count; k++)
{
object[,] array = datalist[k].ToArray();
HSSFSheet newSheet = (HSSFSheet)newBook.CreateSheet(datalist[k].TypeName);//新建工作簿
for (int i = 0; i < array.GetLength(1); i++)
{
HSSFRow newRow = (HSSFRow)newSheet.CreateRow(i);//创建行
for (int j = 0; j < array.GetLength(0); j++)
{
if (array[j, i].GetType() == typeof(String))
{
newSheet.GetRow(i).CreateCell(j).SetCellValue((string)array[j, i]);
}
else
{
newSheet.GetRow(i).CreateCell(j).SetCellValue((int)array[j, i]);
}
}
}
newSheet.AutoSizeColumn(-2);
}
FileStream fs = new FileStream(xlsFile, FileMode.Create);
newBook.Write(fs);
fs.Close();
fs.Dispose();
}
private void AutoAdjustColumnWidth(int columnMax)
{
for (int n = 0; n < columnMax; n++)
{
sheet.AutoSizeColumn(n, true);
}
}
/// <summary>
/// Function to automatically adjust the column width to fit the contents for the specified range of columns (all inputs are 0-based)
/// </summary>
/// <param name="firstCol">The first column</param>
/// <param name="lastCol">The last column</param>
public void AutoFitContents(int firstCol, int lastCol)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
if (firstCol < 0)
{
firstCol = 0;
}
if (firstCol > lastCol)
{
throw new FrameworkException("First column cannot be greater than last column!");
}
for (int currentColumn = firstCol;
currentColumn <= lastCol; currentColumn++)
{
worksheet.AutoSizeColumn(currentColumn);
}
WriteIntoFile(workbook);
}
public void ExportExcel(string fileName, DataGridView dgv, int limit)
{
if (dgv.Rows.Count == 0)
{
MessageBox.Show("请先导入「网管导出的表格」，然后再次尝试");
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2003格式|*.xls";
sfd.FileName = DateTime.Now.ToString("yyyy-MM-dd") + "批量保存表格";
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
int lie = dgv.Columns.Count;
if (limit != 0)
{
lie = limit;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(fileName);
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < lie; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < lie; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
}
}
}
for (int i = 0; i < lie; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
MessageBox.Show("导出成功！", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
wb.Close();
}
public static void ExportLanguages()
{
string path = EditorUtility.SaveFilePanel("Export Languages", "", "", "xls");
if (string.IsNullOrEmpty(path))
{
return;
}
using (Stream writer = File.Create(path))
{
LanguageItem[] allLanguageItems =
TypeHelper.GetAllTypes(AllTypeCategory.All)
.Where(type => typeof(IInfo).IsAssignableFrom(type))
.Select(type => TypeHelper.FindType(InfoResolver.Resolve(type), typeof(LanguageItem <string>)))
.SelectMany(objects => objects)
.Cast <LanguageItem>()
.ToArray();
LanguageEditorInfo languageEditorInfo = EditorInfoResolver.Resolve <LanguageEditorInfo>();
string[] languageNames = languageEditorInfo.Languages.Select(info => info.Name).ToArray();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Languages");
HSSFRow header = (HSSFRow)sheet.CreateRow(0);
HSSFCell hssfCell = (HSSFCell)header.CreateCell(0);
hssfCell.SetCellValue("Item Id");
for (int i = 0; i < languageNames.Length; i++)
{
HSSFCell cell = (HSSFCell)header.CreateCell(i + 1);
cell.SetCellValue(languageNames[i]);
}
for (int i = 0; i < allLanguageItems.Length; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
HSSFCell itemIdcell = (HSSFCell)row.CreateCell(0);
itemIdcell.SetCellValue(allLanguageItems[i].Id);
for (int j = 0; j < languageNames.Length; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j + 1);
if (languageEditorInfo.Languages[j].LanguageDatas.ContainsKey(allLanguageItems[i].Id))
{
object languageData = languageEditorInfo.Languages[j].LanguageDatas[allLanguageItems[i].Id];
cell.SetCellValue(languageData == null?string.Empty:languageData.ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
}
}
for (int i = 0; i < languageNames.Length + 1; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(writer);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
//public string Excute()
//{
//GC.Collect();
//if (!Directory.Exists(_EmployeeExportLocation))
//{
// Directory.CreateDirectory(_EmployeeExportLocation);
//}
//string templocation = _EmployeeExportLocation + "\\绩效评估结果.xls";
//Application excel = new Application();
//_Workbook xBk = excel.Workbooks.Add(_EmployeeTemplateLocation);
//_Worksheet xSt = (_Worksheet) xBk.ActiveSheet;
//try
//{
// InitHrmisQuestionRow(excel);
// ExportALLInfo(excel);
// object nothing = Type.Missing;
// object fileFormat = XlFileFormat.xlExcel8;
// object file = templocation;
// if (File.Exists(file.ToString()))
// {
// File.Delete(file.ToString());
// }
// xBk.SaveAs(file, fileFormat, nothing, nothing, nothing, nothing, XlSaveAsAccessMode.xlNoChange, nothing, nothing, nothing, nothing, nothing);
//}
//finally
//{
// xBk.Close(false, null, null);
// excel.Quit();
// Marshal.ReleaseComObject(xBk);
// Marshal.ReleaseComObject(excel);
// Marshal.ReleaseComObject(xSt);
// GC.Collect();
//}
//return templocation;
//return "";
//}
public MemoryStream Excute()
{
//var workbook = new HSSFWorkbook();
//MemoryStream ms = new MemoryStream();
//HSSFSheet sheet = workbook.CreateSheet("sheet1") as HSSFSheet;
//HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
DataTable dt = new DataTable("Table");
InitHrmisQuestionRow(dt);
ExportALLInfo(dt);
var workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet("sheet1") as HSSFSheet;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
// handling header.
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
for (var i = 0; i < dt.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//_TotalScoreLocation + 4 + _360Question.Count
//Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[i + 1, _TotalScoreLocation + 4 + _360Question.Count]);
//range.Cells.Borders.LineStyle = 1;
//range.EntireColumn.AutoFit();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
public void TestAutoSizeColumn()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("43902.xls");
String sheetName = "my sheet";
HSSFSheet sheet = (HSSFSheet)wb.GetSheet(sheetName);
// Can't use literal numbers for column sizes, as
// will come out with different values on different
// machines based on the fonts available.
// So, we use ranges, which are pretty large, but
// thankfully don't overlap!
int minWithRow1And2 = 6400;
int maxWithRow1And2 = 7800;
int minWithRow1Only = 2750;
int maxWithRow1Only = 3300;
// autoSize the first column and check its size before the merged region (1,0,1,1) is set:
// it has to be based on the 2nd row width
sheet.AutoSizeColumn(0);
Assert.IsTrue(sheet.GetColumnWidth(0) >= minWithRow1And2, "Column autosized with only one row: wrong width");
Assert.IsTrue(sheet.GetColumnWidth(0) <= maxWithRow1And2, "Column autosized with only one row: wrong width");
//Create a region over the 2nd row and auto size the first column
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
sheet.AutoSizeColumn(0);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Check that the autoSized column width has ignored the 2nd row
// because it is included in a merged region (Excel like behavior)
NPOI.SS.UserModel.ISheet sheet2 = wb2.GetSheet(sheetName);
Assert.IsTrue(sheet2.GetColumnWidth(0) >= minWithRow1Only);
Assert.IsTrue(sheet2.GetColumnWidth(0) <= maxWithRow1Only);
// Remove the 2nd row merged region and Check that the 2nd row value is used to the AutoSizeColumn width
sheet2.RemoveMergedRegion(1);
sheet2.AutoSizeColumn(0);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
NPOI.SS.UserModel.ISheet sheet3 = wb3.GetSheet(sheetName);
Assert.IsTrue(sheet3.GetColumnWidth(0) >= minWithRow1And2);
Assert.IsTrue(sheet3.GetColumnWidth(0) <= maxWithRow1And2);
}
public static void createMulitSheetsUsingNPOI(string filepathname, List <allContent> allcontentList)
{
string path = "D:\\导出文件汇总\\";
if (Directory.Exists(path) == false)
{
Directory.CreateDirectory(path);
}
filepathname = path + filepathname;
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//内容表格
foreach (allContent temp in allcontentList)
{
if (temp.contentList.Count <= 0)
{
continue;
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet(temp.sheetName);
int row = temp.contentList.Count + 1;
int column = ((ExportExcelContent)(temp.contentList[0])).contentArray.Count;
for (int ri = 0; ri < row; ri++)
{
sheet.CreateRow(ri);
}
for (int ri = 0; ri < row; ri++)
{
for (int ci = 0; ci < column; ci++)
{
if (ri == 0)
{
sheet.GetRow(ri).CreateCell(ci).SetCellValue(temp.titleList[ci]);
}
else
{
string content = ((ExportExcelContent)(temp.contentList[ri - 1])).contentArray[ci];
sheet.GetRow(ri).CreateCell(ci).SetCellValue(content);
}
}
}
for (int ci = 0; ci < column; ci++)
{
sheet.AutoSizeColumn(ci);
}
}
FileStream file = new FileStream(filepathname, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
MessageBox.Show(filepathname + "导出成功");
}
//第一种情况，预先不知道多少行，但是表格格式统一，表格中没有特殊单元格，比如合并啊，居中啥的，这种情况边框放到最后加
//DataGridView是winform中类型表格的控件
public void ConvertDGVToSheet(DataGridView dv, string sheetName)
{
HSSFSheet sheet = CreateSheet(sheetName);
//冻结标题行
sheet.CreateFreezePane(0, 1, 0, 1);
//写标题
IRow colHeader = sheet.CreateRow(0);
for (int i = 0; i < dv.ColumnCount; i++)
{
if (dv.Columns[i].Visible)
{
colHeader.CreateCell(i).SetCellValue(dv.Columns[i].HeaderText);
}
}
//写内容
//int tmp = 1;
for (int j = 0; j < dv.Rows.Count; j++)
{
IRow row = sheet.CreateRow(j + 1);
for (int k = 0; k < dv.Columns.Count; k++)
{
if (dv.Columns[k].Visible)
{
row.CreateCell(k).SetCellValue(dv.Rows[j].Cells[k].FormattedValue.ToString());
}
}
}
//自动调整列间距
for (int i = 0; i < dv.ColumnCount; i++)
{
sheet.AutoSizeColumn(i);
}
SetCellBorder(sheet, NPOI.SS.UserModel.BorderStyle.Dashed, NPOI.SS.UserModel.BorderStyle.Thin);
}
/// <summary>
/// 將DataTable轉成Stream輸出.
/// </summary>
/// <param name="SourceTable">The source table.</param>
/// <returns></returns>
public static Stream RenderDataTableToExcel(DataTable SourceTable, string sheetName = "sheet1")
{
var workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
// handling header.
foreach (DataColumn column in SourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in SourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
for (var i = 0; i < SourceTable.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
public void SerializeToSheet(HSSFSheet sheet)
{
Dictionary <string, string> parameters = new Dictionary <string, string>();
foreach (ExportRow exportRow in _exports)
{
foreach (string parameterName in exportRow.ParameterNames)
{
parameters[parameterName] = parameterName;
}
}
string[] param = parameters.Select(pair => pair.Key).ToArray();
HSSFRow header = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < param.Length; i++)
{
HSSFCell cell = (HSSFCell)header.CreateCell(i, CellType.String);
cell.CellStyle.ShrinkToFit = true;
cell.SetCellValue(param[i]);
}
int index = 1;
foreach (ExportRow exportRow in _exports)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(index++);
for (int i = 0; i < param.Length; i++)
{
string value = exportRow.ContainsParameter(param[i])
? SerializeObject(exportRow.GetValue(param[i]))
: string.Empty;
HSSFCell cell = (HSSFCell)row.CreateCell(i, CellType.String);
cell.SetCellValue(value);
}
}
for (int i = 0; i < param.Length; i++)
{
sheet.AutoSizeColumn(i);
}
}
public static void AutoSizeALLColumn(this HSSFSheet sheet, int maxColumn)
{
for (int i = 0; i <= maxColumn; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= maxColumn; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, (columnWidth + 1) * 256);
}
}
public static void ExportExcelFileContainCss(DataTable dt, string sheetname, string fileName, string[] columns)
{
InitializeWorkbook();
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet(sheetname);
//设置标题行样式
HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
font.Color = 10;//代表红色
style.SetFont(font);
HSSFRow row;
HSSFCell cell;
HSSFRow rowCap;
//首先设置表列名称
rowCap = (HSSFRow)sheet1.CreateRow(0);
if (columns != null)
{
for (int j = 0; j < columns.Length; j++)
{
cell = (HSSFCell)rowCap.CreateCell(j);
cell.SetCellValue(columns[j]);
sheet1.AutoSizeColumn(j);
}
}
else
{
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = (HSSFCell)rowCap.CreateCell(j);
cell.SetCellValue(dt.Columns[j].Caption);
sheet1.AutoSizeColumn(j);
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
row = (HSSFRow)sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = (HSSFCell)row.CreateCell(j);
if (dr[j].ToString().Contains("@?@"))
{
dr[j] = dr[j].ToString().Replace("@?@", "");
cell.CellStyle = style;
}
cell.SetCellValue(dr[j].ToString());
}
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
{
int columnWidth = sheet1.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet1.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet1.GetRow(rowNum) == null)
{
currentRow = sheet1.CreateRow(rowNum);
}
else
{
currentRow = sheet1.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;
}
}
}
sheet1.SetColumnWidth(columnNum, columnWidth * 350);
}
SaveToFile(fileName);
}
public static void ToExcel2003 <T>(List <T> dataSource, string path, List <string> attries, List <string> headers)
{
if (dataSource == null || !dataSource.Any())
{
throw new Exception("dataSource is null.");
}
HSSFWorkbook wb = new HSSFWorkbook(); //创建一个工作薄
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; //在工作薄中创建一个工作表
HSSFRow rw = sheet.CreateRow(0) as HSSFRow;
var patriarch = sheet.CreateDrawingPatriarch();
for (int i = 0; i < headers.Count; i++) //循环一个表头来创建第一行的表头
{
rw.CreateCell(i).SetCellValue(headers[i]);
}
Type t = typeof(T); //获取得泛型集合中的实体， 返回T的类型
PropertyInfo[] properties = t.GetProperties(); //返回当前获得实体后 实体类型中的所有公共属性
for (int i = 0; i < dataSource.Count; i++) //循环实体泛型集合
{
rw = sheet.CreateRow(i + 1) as HSSFRow; //创建一个新行，把传入集合中的每条数据创建一行
foreach (PropertyInfo property in properties) //循环得到的所有属性（想要把里面指定的属性值导出到Excel文件中）
{
for (int j = 0; j < attries.Count; j++) //循环需要导出属性值 的 属性名
{
string attry = attries[j]; //获得一个需要导入的属性名；
if (string.Compare(property.Name.ToUpper(), attry.ToUpper()) == 0) //如果需要导出的属性名和当前循环实体的属性名一样，
{
object objValue = property.GetValue(dataSource[i], null); //获取当前循环的实体属性在当前实体对象（arr[i]）的值
if (objValue != null && (objValue.GetType().Name == "Bitmap" || objValue.GetType().Name == "Image"))
{
//- 插入图片到 Excel，并返回一个图片的标识
var handle = (objValue as Bitmap).GetHbitmap();
using (Bitmap newBmp = Image.FromHbitmap(handle))
{
MemoryStream ms = new MemoryStream();
newBmp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bytes = ms.GetBuffer();
ms.Close();
var pictureIdx = wb.AddPicture(bytes, PictureType.JPEG);
//- 创建图片的位置
var anchor = new HSSFClientAnchor(
0, 0, //- 上左 到 上右 的位置，是基于下面的行列位置
0, 0, //- 下左 到 下右 的位置，是基于下面的行列位置
j, i + 1,
j + 1, i + 2);
//- 图片输出的位置这么计算的：
//- 假设我们要将图片放置于第 5(E) 列的第 2 行
//- 对应索引为是 4 : 1 （默认位置）
//- 放置的位置就等于（默认位置）到（默认位置各自加上一行、一列）
var pic = patriarch.CreatePicture(anchor, pictureIdx);//- 使用绘画器绘画图片
sheet.SetColumnWidth(j, 100 * 36);
rw.HeightInPoints = 100 * 0.75f;
bytes = null;
}
DeleteObject(handle);
}
else
{
rw.CreateCell(j).SetCellValue((objValue == null) ? string.Empty : objValue.ToString());//创建单元格并进行赋值
sheet.AutoSizeColumn(j);
}
}
}
}
}
using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
wb.Write(fs);
}
}
private void DgvToXls(DataGridView dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出数据到本地计算机";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName == "")
{
return;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Columns[j].HeaderText == "日期" && dgv.Rows[i].Cells[j].Value != DBNull.Value)
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
else
{
cell.SetCellValue((dgv.Rows[i].Cells[j].Value).ToString());
}
//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"));
//}
//else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Time"))
//{
// cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("HH-mm-ss"));
//}
}
}
}
for (int i = 0; i < dgv.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(saveFileDialog.FileName, FileMode.Create))
{
wb.Write(fs);
}
#endregion
MessageBox.Show("恭喜，导出成功");
}
public void GridToExcel(string fileName, DataGridView dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2003格式|*.xls";
sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssms");
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
//if (fileName.IndexOf(".xlsx") > 0) // 2007版本
// workbook = new XSSFWorkbook(fs);
//else if (fileName.IndexOf(".xls") > 0) // 2003版本
// workbook = new HSSFWorkbook(fs);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(fileName);
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)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);
}
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
MessageBox.Show("导出成功！", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static void GridToExcel(string fileName, DataTable dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2003格式|*.xls";
sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssms");
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(fileName);
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
//样式
ICellStyle cellStyle = Getcellstyle(wb);
for (int i = 0; i < dgv.Columns.Count; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].Caption);
headCell.CellStyle = cellStyle;
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (dgv.Rows[i][j] == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Rows[i][j].GetType().FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgv.Rows[i][j]));
}
else if (dgv.Rows[i][j].GetType().FullName.Contains("System.String"))
{
cell.SetCellValue(dgv.Rows[i][j].ToString());
}
else if (dgv.Rows[i][j].GetType().FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgv.Rows[i][j]));
}
else if (dgv.Rows[i][j].GetType().FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i][j]));
}
else if (dgv.Rows[i][j].GetType().FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i][j]));
}
else if (dgv.Rows[i][j].GetType().FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
cell.SetCellValue(dgv.Rows[i][j].ToString());
}
}
//cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle = cellStyle;
}
}
for (int i = 0; i < dgv.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
MessageHelper.ShowInfo("导出成功！");
//MessageBox.Show("导出成功！", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public bool GridToExcel(string fileName, DataGridView dgvList)
{
if (dgvList.Rows.Count == 0)
{
return(false);
}
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "Excel 2003格式|*.xls";
saveFile.FileName = fileName + DateTime.Now.ToString("yyyyMMddHH");
if (saveFile.ShowDialog() != DialogResult.OK)
{
return(false);
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(fileName);
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgvList.ColumnCount; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgvList.Columns[i].HeaderText);
}
for (int i = 0; i < dgvList.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgvList.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (dgvList.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgvList.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgvList.Rows[i].Cells[j].Value));
}
else if (dgvList.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(dgvList.Rows[i].Cells[j].Value.ToString());
}
else if (dgvList.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgvList.Rows[i].Cells[j].Value));
}
else if (dgvList.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgvList.Rows[i].Cells[j].Value));
}
else if (dgvList.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgvList.Rows[i].Cells[j].Value));
}
else if (dgvList.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgvList.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
}
}
}
for (int i = 0; i < dgvList.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = new FileStream(saveFile.FileName, FileMode.Create))
{
workbook.Write(fs);
}
return(true);
}
/// <summary>
/// 将ExDataGridView中的数据导出到Excel文件中
/// </summary>
/// <param name="gridview">要导出的ExDataGridView</param>
/// <returns></returns>
public static bool ExDataGridViewToExcel(ExDataGridView gridview)
{
//导出为xls格式用HSSF，xlsx用XSSF。
System.Windows.Forms.SaveFileDialog SFD = new System.Windows.Forms.SaveFileDialog();
string FileName = "";
if (gridview.Columns.Count == 0)
{
throw new Exception("没有可以导出的数据！");
}
SFD.Filter = "Excel文件(*.xls)|*.xls";
SFD.Title = "导出Excel表";
if (SFD.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return(false);
}
FileName = SFD.FileName.ToString().Trim();
try
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("数据页" + DateTime.Now.ToString("yyyyMMddHHmmssms"));
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < gridview.Columns.Count; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(gridview.Columns[i].HeaderText);
}
for (int i = 0; i < gridview.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < gridview.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (gridview.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(gridview.Rows[i].Cells[j].Value.ToString());
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(gridview.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
}
}
}
for (int i = 0; i < gridview.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = new FileStream(FileName, FileMode.Create))
{
wb.Write(fs);
}
MessageBox.Show("导出成功！", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(true);
}
//title list的长度要保证与内容contentArray的长度一致, 一个文件包含多个sheet的尝试
public static void createExcelListUsingNPOI(string filepathname, debitnotsSheet3 debitnots, List <allContent> allcontentList)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//首页sheet
HSSFSheet firstsheet = (HSSFSheet)hssfworkbook.CreateSheet("Debit NOTS");
for (int i = 0; i <= 30; i++)
{
firstsheet.CreateRow(i);
}
firstsheet.GetRow(0).CreateCell(2).SetCellValue(debitnots.titleC1);
HSSFCellStyle cellStyletitle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
IFont titlefont = hssfworkbook.CreateFont();
titlefont.FontHeightInPoints = 20;
titlefont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titlefont.FontName = "宋体";
cellStyletitle.SetFont(titlefont);
firstsheet.GetRow(0).GetCell(2).CellStyle = cellStyletitle;
firstsheet.GetRow(1).CreateCell(0).SetCellValue(debitnots.addressA2);
firstsheet.GetRow(2).CreateCell(0).SetCellValue(debitnots.telA3);
firstsheet.GetRow(4).CreateCell(0).SetCellValue(debitnots.billto1A5);
firstsheet.GetRow(5).CreateCell(0).SetCellValue(debitnots.billto2A6);
firstsheet.GetRow(6).CreateCell(0).SetCellValue(debitnots.billto3A7);
firstsheet.GetRow(7).CreateCell(0).SetCellValue(debitnots.billto4A8);
firstsheet.GetRow(8).CreateCell(0).SetCellValue(debitnots.telA9);
firstsheet.GetRow(9).CreateCell(0).SetCellValue(debitnots.faxA10);
firstsheet.GetRow(4).CreateCell(5).SetCellValue(debitnots.dateF5);
firstsheet.GetRow(4).CreateCell(6).SetCellValue(debitnots.dateG5);
firstsheet.GetRow(5).CreateCell(5).SetCellValue(debitnots.invF6);
HSSFCellStyle cellStylesub = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
IFont subfont = hssfworkbook.CreateFont();
subfont.FontHeightInPoints = 9;
subfont.FontName = "宋体";
cellStylesub.SetFont(subfont);
firstsheet.GetRow(1).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(2).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(4).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(5).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(6).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(7).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(8).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(9).GetCell(0).CellStyle = cellStylesub;
firstsheet.GetRow(4).GetCell(5).CellStyle = cellStylesub;
firstsheet.GetRow(4).GetCell(6).CellStyle = cellStylesub;
firstsheet.GetRow(5).GetCell(5).CellStyle = cellStylesub;
HSSFCellStyle cellStyleline = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
cellStyleline.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;//A11-G11,A26-G26
firstsheet.GetRow(10).CreateCell(0);
firstsheet.GetRow(10).CreateCell(1);
firstsheet.GetRow(10).CreateCell(2);
firstsheet.GetRow(10).CreateCell(3);
firstsheet.GetRow(10).CreateCell(4);
firstsheet.GetRow(10).CreateCell(5);
firstsheet.GetRow(10).CreateCell(6);
firstsheet.GetRow(10).GetCell(0).CellStyle = cellStyleline;
firstsheet.GetRow(10).GetCell(1).CellStyle = cellStyleline;
firstsheet.GetRow(10).GetCell(2).CellStyle = cellStyleline;
firstsheet.GetRow(10).GetCell(3).CellStyle = cellStyleline;
firstsheet.GetRow(10).GetCell(4).CellStyle = cellStyleline;
firstsheet.GetRow(10).GetCell(5).CellStyle = cellStyleline;
firstsheet.GetRow(10).GetCell(6).CellStyle = cellStyleline;
firstsheet.GetRow(25).CreateCell(0);
firstsheet.GetRow(25).CreateCell(1);
firstsheet.GetRow(25).CreateCell(2);
firstsheet.GetRow(25).CreateCell(3);
firstsheet.GetRow(25).CreateCell(4);
firstsheet.GetRow(25).CreateCell(5);
firstsheet.GetRow(25).CreateCell(6);
firstsheet.GetRow(25).GetCell(0).CellStyle = cellStyleline;
firstsheet.GetRow(25).GetCell(1).CellStyle = cellStyleline;
firstsheet.GetRow(25).GetCell(2).CellStyle = cellStyleline;
firstsheet.GetRow(25).GetCell(3).CellStyle = cellStyleline;
firstsheet.GetRow(25).GetCell(4).CellStyle = cellStyleline;
firstsheet.GetRow(25).GetCell(5).CellStyle = cellStyleline;
firstsheet.GetRow(25).GetCell(6).CellStyle = cellStyleline;
firstsheet.GetRow(13).CreateCell(0).SetCellValue(debitnots.contentA14);
firstsheet.GetRow(14).CreateCell(2).SetCellValue(debitnots.contentC14);
firstsheet.GetRow(13).CreateCell(3).SetCellValue(debitnots.contentD14);
firstsheet.GetRow(13).CreateCell(4).SetCellValue(debitnots.contentE14);
firstsheet.GetRow(14).CreateCell(3).SetCellValue(debitnots.contentD15);
firstsheet.GetRow(14).CreateCell(4).SetCellValue(debitnots.contentE15);
firstsheet.GetRow(15).CreateCell(1).SetCellValue(debitnots.contentB16);
firstsheet.GetRow(15).CreateCell(3).SetCellValue(debitnots.contentD16);
firstsheet.GetRow(15).CreateCell(4).SetCellValue(debitnots.contentE16);
firstsheet.GetRow(15).CreateCell(5).SetCellValue(debitnots.contentF16);
firstsheet.GetRow(16).CreateCell(1).SetCellValue(debitnots.contentB17);
firstsheet.GetRow(16).CreateCell(3).SetCellValue(debitnots.contentD17);
firstsheet.GetRow(16).CreateCell(4).SetCellValue(debitnots.contentE17);
firstsheet.GetRow(16).CreateCell(5).SetCellValue(debitnots.contentF17);
firstsheet.GetRow(19).CreateCell(1).SetCellValue(debitnots.contentB20);
firstsheet.GetRow(19).CreateCell(3).SetCellValue(debitnots.contentD20);
firstsheet.GetRow(19).CreateCell(4).SetCellValue(debitnots.contentE20);
firstsheet.GetRow(19).CreateCell(5).SetCellValue(debitnots.contentF20);
firstsheet.GetRow(21).CreateCell(4).SetCellValue(debitnots.contentE22);
firstsheet.GetRow(21).CreateCell(5).SetCellValue(debitnots.contentF22);
firstsheet.GetRow(23).CreateCell(1).SetCellValue(debitnots.contentB24);
firstsheet.GetRow(23).CreateCell(4).SetCellValue(debitnots.contentE24);
firstsheet.GetRow(23).CreateCell(5).SetCellValue(debitnots.contentF24);
firstsheet.GetRow(24).CreateCell(1).SetCellValue(debitnots.contentB25);
firstsheet.GetRow(24).CreateCell(4).SetCellValue(debitnots.contentE25);
firstsheet.GetRow(24).CreateCell(5).SetCellValue(debitnots.contentF25);
firstsheet.GetRow(26).CreateCell(4).SetCellValue(debitnots.contentE27);
firstsheet.GetRow(26).CreateCell(5).SetCellValue(debitnots.contentF27);
firstsheet.GetRow(29).CreateCell(4).SetCellValue(debitnots.contentE30);
firstsheet.GetRow(30).CreateCell(4).SetCellValue(debitnots.contentE31);
HSSFCellStyle cellStylecontent = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
IFont contentfont = hssfworkbook.CreateFont();
contentfont.FontHeightInPoints = 12;
contentfont.FontName = "宋体";
cellStylecontent.SetFont(contentfont);
firstsheet.GetRow(13).GetCell(0).CellStyle = cellStylecontent;
firstsheet.GetRow(14).GetCell(2).CellStyle = cellStylecontent;
firstsheet.GetRow(13).GetCell(3).CellStyle = cellStylecontent;
firstsheet.GetRow(13).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(14).GetCell(3).CellStyle = cellStylecontent;
firstsheet.GetRow(14).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(15).GetCell(1).CellStyle = cellStylecontent;
firstsheet.GetRow(15).GetCell(3).CellStyle = cellStylecontent;
firstsheet.GetRow(15).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(15).GetCell(5).CellStyle = cellStylecontent;
firstsheet.GetRow(16).GetCell(1).CellStyle = cellStylecontent;
firstsheet.GetRow(16).GetCell(3).CellStyle = cellStylecontent;
firstsheet.GetRow(16).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(16).GetCell(5).CellStyle = cellStylecontent;
firstsheet.GetRow(19).GetCell(1).CellStyle = cellStylecontent;
firstsheet.GetRow(19).GetCell(3).CellStyle = cellStylecontent;
firstsheet.GetRow(19).GetCell(3).CellStyle = cellStylecontent;
firstsheet.GetRow(19).GetCell(5).CellStyle = cellStylecontent;
firstsheet.GetRow(21).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(21).GetCell(5).CellStyle = cellStylecontent;
firstsheet.GetRow(23).GetCell(1).CellStyle = cellStylecontent;
firstsheet.GetRow(23).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(23).GetCell(5).CellStyle = cellStylecontent;
firstsheet.GetRow(24).GetCell(1).CellStyle = cellStylecontent;
firstsheet.GetRow(24).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(24).GetCell(5).CellStyle = cellStylecontent;
//firstsheet.GetRow(26).GetCell(4).CellStyle = cellStylecontent;
//firstsheet.GetRow(26).GetCell(5).CellStyle = cellStylecontent;
firstsheet.GetRow(29).GetCell(4).CellStyle = cellStylecontent;
firstsheet.GetRow(30).GetCell(4).CellStyle = cellStylecontent;
//HSSFCellStyle cellStyleback = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
//cellStyleback.FillBackgroundColor = HSSFColor.Yellow.Index;
//cellStyleback.FillForegroundColor = HSSFColor.Yellow.Index;
//cellStyleback.SetFont(contentfont);
//firstsheet.GetRow(26).GetCell(5).CellStyle = cellStyleback;
//firstsheet.GetRow(26).GetCell(4).CellStyle = cellStyleback;
//其他内容表格
foreach (allContent temp in allcontentList)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet(temp.sheetName);
int row = temp.contentList.Count + 1;
int column = ((ExportExcelContent)(temp.contentList[0])).contentArray.Count;
for (int ri = 0; ri < row; ri++)
{
sheet.CreateRow(ri);
}
for (int ri = 0; ri < row; ri++)
{
for (int ci = 0; ci < column; ci++)
{
if (ri == 0)
{
sheet.GetRow(ri).CreateCell(ci).SetCellValue(temp.titleList[ci]);
}
else
{
string content = ((ExportExcelContent)(temp.contentList[ri - 1])).contentArray[ci];
sheet.GetRow(ri).CreateCell(ci).SetCellValue(content);
}
}
}
for (int ci = 0; ci < column; ci++)
{
sheet.AutoSizeColumn(ci);
}
}
//hssfworkbook.CreateSheet("Sheet1");
//sheet.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");//npoi 的下标从0开始的，原始的从1开始
//HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
//cell.SetCellValue(new DateTime(2008, 5, 5));
//set dateformat
//HSSFCellStyle cellStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
//HSSFDataFormat format = (HSSFDataFormat)hssfworkbook.CreateDataFormat();
//cellStyle.DataFormat = format.GetFormat("yyyy年m月d日");
//cell.CellStyle = cellStyle;
// Create arow and put some cells in it. Rows are 0 based.
//HSSFRow row = (HSSFRow)sheet.CreateRow(1);
//// Create acell and put a value in it.
//cell = (HSSFCell)row.CreateCell(1);
//// Style thecell with borders all around.
//HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
//sheet2.CreateRow(0).CreateCell(0).SetCellValue("This2 is a Sample");//npoi 的下标从0开始的，原始的从1开始
//HSSFSheet sheet3 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet3");
//sheet3.CreateRow(0).CreateCell(0).SetCellValue("This3 is a Sample");//npoi 的下标从0开始的，原始的从1开始
FileStream file = new FileStream(filepathname, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
MessageBox.Show(filepathname + "导出成功");
}
public static void ExportExcel(DataGridView dgv, string fileName = "", string fontname = "微软雅黑", short fontsize = 10)
{
//检测是否有数据
if (dgv.RowCount <= 0)
{
return;
}
//创建主要对象
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight");
//设置字体，大小，对齐方式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = fontname;
font.FontHeightInPoints = fontsize;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//首行填充黄色
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.FillForegroundColor = IndexedColors.Yellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.SetFont(font);
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//添加表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.GetCell(i).CellStyle = headerStyle;
}
//注释的这行是设置筛选的
//sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count));
//添加列及内容
for (int i = 0; i < dgv.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);//先来个常规自适应
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
string ValueType = dgv.Rows[i].Cells[j].Value.GetType().ToString();
string Value = dgv.Rows[i].Cells[j].Value.ToString();
switch (ValueType)
{
case "System.String": //字符串类型
dataRow.CreateCell(j).SetCellValue(Value);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(Value, out dateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(Value, out boolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(Value, out intV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(Value, out doubV);
dataRow.CreateCell(j).SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
dataRow.CreateCell(j).SetCellValue("");
break;
default:
dataRow.CreateCell(j).SetCellValue("");
break;
}
dataRow.GetCell(j).CellStyle = style;
//设置宽度
sheet.SetColumnWidth(j, (Value.Length + 10) * 300);
//首行冻结
sheet.CreateFreezePane(dgv.Columns.Count, 1);
}
}
//保存文件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
MemoryStream ms = new MemoryStream();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件占用，请关闭文件后再继续操作！ " + saveFileName);
workbook = null;
ms.Close();
ms.Dispose();
return;
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show("导出完成！", "", MessageBoxButtons.OK);
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
}
public static void ExportToExcel1 <T>(string excelName, List <Tuple <string, string, double, Type> > headers, ObservableCollection <T> RecordCollection)
{
string excelPath = ConfigurationManager.AppSettings["ExcelPath"];
if (excelPath.Last() != System.IO.Path.DirectorySeparatorChar)
{
excelPath += System.IO.Path.DirectorySeparatorChar;
}
string excelFile = string.Format("{0}{1}", excelPath, excelName);
string sheetName = "Sheet1";
if (File.Exists(excelFile))
{
File.Delete(excelFile);
}
HSSFWorkbook wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());
HSSFSheet sh = (HSSFSheet)wb.CreateSheet(sheetName);
for (int i = 1; i <= RecordCollection.Count; i++)
{
var item = RecordCollection[i - 1];
if (sh.GetRow(i) == null)
{
sh.CreateRow(i);
}
for (int j = 0; j < headers.Count; j++)
{
if (sh.GetRow(i).GetCell(j) == null)
{
sh.GetRow(i).CreateCell(j);
}
string sValue = "";
var y = typeof(T).InvokeMember(headers[j].Item2.ToString(), BindingFlags.GetProperty, null, item, null);
sValue = (y == null) ? "" : y.ToString();
sh.GetRow(i).GetCell(j).SetCellValue(sValue);
}
}
var r = sh.CreateRow(0);
for (int j = 0; j < headers.Count; j++)
{
if (sh.GetRow(0).GetCell(j) == null)
{
sh.GetRow(0).CreateCell(j);
}
sh.GetRow(0).GetCell(j).SetCellValue(headers[j].Item1);
sh.AutoSizeColumn(j);
}
using (var fs = new FileStream(excelFile, FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
ExploreFile(excelFile);
}
public ActionResult ExportData(string queryJson)
{
try
{
Pagination pagination = new Pagination();
pagination.sord = "asc";
pagination.records = 0;
pagination.page = 1;
pagination.rows = 1000000000;
var watch = CommonHelper.TimerStart();
//pagination.p_kid = "v.USERID";
//pagination.p_fields = " v.FULLNAME,v.DEPARTMENTID,v.DEPARTMENTCODE, v.REALNAME,v.DUTYID,v.DUTYNAME,v.REMARK ,v.DEPTTYPE,v.dkremark,v.DEPTSORT , v.SORTCODE,1 as personcount ";
//pagination.p_tablename = @" V_ATTENDANCEWARNING v";
//pagination.conditionJson = " v.DEPARTMENTID not in ('0') ";
//Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
var exportTable = hikinoutlogbll.GetAttendanceWarningPageList(pagination, queryJson);
////设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "人员考勤告警";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 16;
//excelconfig.FileName = "人员考勤告警.xls";
//excelconfig.IsAllSizeColumn = true;
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
////需跟数据源列顺序保持一致
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fullname", ExcelColumn = "单位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname", ExcelColumn = "姓名", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname", ExcelColumn = "岗位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dkremark", ExcelColumn = "备注", Width = 250 });
////调用导出方法
//ExcelHelper.ExcelDownload(exportTable, excelconfig); ////设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "人员考勤告警";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 16;
//excelconfig.FileName = "人员考勤告警.xls";
//excelconfig.IsAllSizeColumn = true;
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
////需跟数据源列顺序保持一致
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fullname", ExcelColumn = "单位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname", ExcelColumn = "姓名", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname", ExcelColumn = "岗位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dkremark", ExcelColumn = "备注", Width = 250 });
////调用导出方法
//ExcelHelper.ExcelDownload(exportTable, excelconfig);
//导出excel
string title = "人员考勤告警";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 30 * 20;
int column = exportTable.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 22;
font.FontName = "宋体";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
headerFont.FontHeightInPoints = 16;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("单位名称");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("岗位名称");
row1.GetCell(2).CellStyle = headerStyle;
row1.CreateCell(3).SetCellValue("备注");
row1.GetCell(3).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
font1.Boldweight = 25;
font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < exportTable.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(exportTable.Rows[i]["fullname"].ToString() + "(" + exportTable.Rows[i]["personcount"].ToString() + ")");
rowTemp.CreateCell(1).SetCellValue(exportTable.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(exportTable.Rows[i]["dutyname"].ToString());
rowTemp.CreateCell(3).SetCellValue(exportTable.Rows[i]["dkremark"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
rowTemp.GetCell(3).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
//合并单元格
MergeCells(sheet, exportTable, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex)
{
}
return(Success("导出成功。"));
}
public void ExportToExcel(DataTable dtTemp, string filename, string sheetname, string sheetHeader)
{
try
{
string Today = DateTime.Now.ToString("d MMM yyyy");
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
string FileName = "";
if (filename.EndsWith(".xls"))
{
FileName = filename;
}
else
{
FileName = filename + ".xls";
}
HSSFSheet sheet1 = (NPOI.HSSF.UserModel.HSSFSheet)hssfworkbook.CreateSheet(sheetname);
sheet1.DisplayGridlines = true;
sheet1.Footer.Right = "Page " + HSSFFooter.Page;
sheet1.SetMargin(MarginType.FooterMargin, (double)0.25);
#region "Print Setup"
sheet1.SetMargin(MarginType.RightMargin, (double)0.25);
sheet1.SetMargin(MarginType.TopMargin, (double)0.75);
sheet1.SetMargin(MarginType.LeftMargin, (double)0.50);
sheet1.SetMargin(MarginType.BottomMargin, (double)0.75);
sheet1.PrintSetup.Copies = 1;
sheet1.PrintSetup.Landscape = false;
sheet1.PrintSetup.PaperSize = 9;
sheet1.PrintSetup.Scale = 90;
sheet1.IsPrintGridlines = true;
sheet1.Autobreaks = true;
sheet1.FitToPage = false;
#endregion
HSSFRow rowHeader = (NPOI.HSSF.UserModel.HSSFRow)sheet1.CreateRow(0);
#region "Header"
for (int k = 0; k < dtTemp.Columns.Count; k++)
{
String columnName = dtTemp.Columns[k].ToString().Replace("_", " ");
HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)rowHeader.CreateCell(k);
var headerLabelCellStyle = hssfworkbook.CreateCellStyle();
headerLabelCellStyle.LeftBorderColor = HSSFColor.Black.Index;
headerLabelCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerLabelCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerLabelCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headerLabelCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerLabelCellStyle.BottomBorderColor = HSSFColor.Grey50Percent.Index;
headerLabelCellStyle.TopBorderColor = HSSFColor.Grey50Percent.Index;
headerLabelCellStyle.LeftBorderColor = HSSFColor.Grey50Percent.Index;
headerLabelCellStyle.RightBorderColor = HSSFColor.Grey50Percent.Index;
headerLabelCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
headerLabelCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
headerLabelCellStyle.ShrinkToFit = true;
var formate = hssfworkbook.CreateDataFormat();
var headerLabelFont = hssfworkbook.CreateFont();
headerLabelFont.FontHeight = 200;
headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;
var headerDataFormat = hssfworkbook.CreateDataFormat();
headerLabelCellStyle.SetFont(headerLabelFont);
cell.SetCellValue(columnName);
cell.CellStyle = headerLabelCellStyle;
rowHeader.Height = 400;
}
#endregion
#region "Row"
int count = 1;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
var RowCellStyle = hssfworkbook.CreateCellStyle();
HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)sheet1.CreateRow(count);
for (int l = 0; l < dtTemp.Columns.Count; l++)
{
HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(l);
String columnName = dtTemp.Columns[l].ToString();
cell.CellStyle.WrapText = true;
cell.SetCellValue(Convert.ToString(dtTemp.Rows[i][columnName]));
cell.CellStyle = RowCellStyle;
cell.CellStyle.WrapText = true;
}
count++;
}
#endregion
#region "Set Columns width"
for (int d = 0; d < dtTemp.Columns.Count; d++)
{
string columnName = dtTemp.Columns[d].ToString();
if (columnName == "Notification")
{
sheet1.SetColumnWidth(d, 35 * 300);
}
else if (columnName == "Processing_Status")
{
sheet1.SetColumnWidth(d, 35 * 300);
}
else if (columnName == "Action_Status")
{
sheet1.SetColumnWidth(d, 35 * 300);
}
else
{
sheet1.AutoSizeColumn(d);
}
}
#endregion
System.Web.HttpResponse Response = System.Web.HttpContext.Current.Response;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", FileName));
Response.Clear();
Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());
Response.Flush();
Response.End();
}
catch (Exception ex)
{
string s = ex.Message;
}
}
/// <summary>
/// 生成模板
/// </summary>
/// <param name="displayName">文件名</param>
/// <returns>生成的模版文件</returns>
public byte[] GenerateTemplate(out string displayName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
InitExcelData();
CreateDataTable(); //add by dufei
SetTemplateDataValus(); //add by dufei
if (!string.IsNullOrEmpty(FileDisplayName))
{
displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
else
{
displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
//模板sheet页
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName);
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.HeightInPoints = 20;
HSSFSheet enumSheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0);
enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]);
enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]);
enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号
HSSFSheet dataSheet = (HSSFSheet)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;
bool IsProtect = false;
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
ColumnDataType dateType = excelPropety.DataType;
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 添加模版数据 add by dufei
if (TemplateDataTable.Rows.Count > 0)
{
for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
{
DataRow tableRow = TemplateDataTable.Rows[i];
HSSFRow dataRow = (HSSFRow)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");
}
workbook.SetSheetHidden(1, true);
workbook.SetSheetHidden(2, true);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
private void execute()
{
try
{
HSSFWorkbook inputBook = new HSSFWorkbook(new FileStream(INPUT_URL, FileMode.Open)); //input不可外流，放在GitHub外層
HSSFSheet inputSheet = (HSSFSheet)inputBook.GetSheetAt(0); //目前只取第一分頁sheet0
//提早取得名稱
//取得資料
int iNumRow = inputSheet.LastRowNum;
for (int i = 0; i < iNumRow; ++i)
{
//保留標題列略過
if (i < this.keepRow)
{
continue;
}
HSSFWorkbook outputBook = new HSSFWorkbook();
HSSFSheet outputSheet = (HSSFSheet)outputBook.CreateSheet("sheet1"); // 在 Excel 工作簿中建立工作表，名稱為 Sheet1
HSSFRow iCurRow = (HSSFRow)inputSheet.GetRow(i); //取得第N列資料
//標題列數(注意:最後一條保留列為資料標題)
int titleCellCount = 0;
int ci = 0;
//先寫入保留標題列---------------------------------------------------------------------------------------
for (int ki = 0; ki < this.keepRow; ++ki)
{
//查詢保留列
HSSFRow iTitleRow = (HSSFRow)inputSheet.GetRow(ki);
//保留列有多少欄?
titleCellCount = iTitleRow.LastCellNum;
//建立保留列
HSSFRow oTitleRow = (HSSFRow)outputSheet.CreateRow(ki);
//設定同等列高
oTitleRow.Height = iTitleRow.Height;
for (ci = 0; ci < titleCellCount; ++ci)
{
//input.J copy to output.J
ICell inTItleCell = iTitleRow.GetCell(ci);
if (inTItleCell != null)
{
ICell oTitleCell = oTitleRow.CreateCell(ci);
oTitleCell.SetCellValue(inTItleCell.StringCellValue);
//複製欄格式
HSSFCellStyle newTitleCellStyle = (HSSFCellStyle)outputBook.CreateCellStyle();
newTitleCellStyle.CloneStyleFrom(inTItleCell.CellStyle);
oTitleCell.CellStyle = newTitleCellStyle;
}
}
}
//寫入資料---------------------------------------------------------------------------------------
//建立員工資料列
string name = "temp";
string apartment = "temp";
HSSFRow oDataRow = (HSSFRow)outputSheet.CreateRow(this.keepRow);
oDataRow.Height = iCurRow.Height;
for (ci = 0; ci < titleCellCount; ++ci)
{
ICell inDataCell = iCurRow.GetCell(ci);
if (inDataCell == null)
{
continue;
}
//取得名字
if (ci == this.nameCell)
{
name = inDataCell.StringCellValue;
}
//取得部門
if (ci == this.nameCell + 1)
{
apartment = inDataCell.StringCellValue;
}
ICell oDataCell = oDataRow.CreateCell(ci);
if (this.config.needToSkipCol(name, ci + 1))
{
outputSheet.SetColumnHidden(ci, true);
}
//到職日特別處理
if (ci == 0)
{
var value = inDataCell.DateCellValue;
oDataCell.SetCellValue(value.ToShortDateString());//轉換為2014/12/19
}
//數字
else if (inDataCell.CellType == NPOI.SS.UserModel.CellType.Numeric || inDataCell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
var value = inDataCell.NumericCellValue;
oDataCell.SetCellValue(value);
}
else
{
oDataCell.SetCellValue(inDataCell.StringCellValue);
}
HSSFCellStyle newCellStyle = (HSSFCellStyle)outputBook.CreateCellStyle();
newCellStyle.CloneStyleFrom(inDataCell.CellStyle);
oDataCell.CellStyle = newCellStyle;
outputSheet.AutoSizeColumn(ci);
//oDataCell.CellStyle.WrapText = true;
}
//刪除-------------------------------------------------------------------
for (int ri = 0; ri <= outputSheet.LastRowNum; ++ri)
{
HSSFRow eachRow = (HSSFRow)outputSheet.GetRow(ri);//取得第N列資料
titleCellCount = eachRow.LastCellNum;
for (ci = titleCellCount - 1; ci > -1; --ci)
{
if (this.config.needToSkipCol(name, ci + 1))
{
ICell eachCell = eachRow.GetCell(ci);
if (eachCell == null)
{
eachCell = eachRow.CreateCell(ci);
}
eachRow.RemoveCell(eachCell);
}
}
}
//output不可外流，放在GitHub外層
string fileName = OUTPUT_URL;
fileName = fileName.Replace("#1", apartment);
fileName = fileName.Replace("#2", name);
Directory.CreateDirectory(Path.GetDirectoryName(fileName));
FileStream outputStream = new FileStream(fileName, FileMode.Create);
outputBook.Write(outputStream);
outputBook.WriteProtectWorkbook("123", "456");
outputStream.Close();
}
MessageBox.Show("薪資拆分成功!");
System.Windows.Forms.Application.Exit();//自動關閉APP
}
catch (Exception error)
{
MessageBox.Show("異常錯誤 " + error.Message);
}
}
public async Task ListToSheet <T>(HSSFWorkbook workbook, List <T> list, HSSFCellStyle headStyle, string sheetName)
{
//值类型直接返回第一列
Type tp = typeof(T);
//属性列表
PropertyInfo[] properties = tp.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance);
//property.Name是属性的英文名，怎么转换成中文？使用DescriptionAttribute特性
List <string> fieldStringArray = new List <string>();
foreach (var property in properties)
{
fieldStringArray.Add(property.GetEnumDescription());
}
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
int fieldCount = fieldStringArray.Count;
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
for (int i = 0; i < fieldCount; i++)
{
#region 表头及样式
headerRow.CreateCell(i).SetCellValue(fieldStringArray[i]);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.AutoSizeColumn(i);
#endregion
}
var count = list.Count();
for (int i = 0; i < count; i++)
{
#region 单元格样式
ICellStyle styleCell = workbook.CreateCellStyle();
styleCell.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中
styleCell.VerticalAlignment = VerticalAlignment.Center; //垂直居中
#endregion
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(i + 1);
var data = list[i];
for (int cellIndex = 0; cellIndex < fieldCount; cellIndex++)
{
var property = properties[cellIndex];
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(cellIndex, CellType.String);
newCell.SetCellValue(property.GetValue(data).ToString());
newCell.CellStyle = styleCell;
}
}
#region 统一设置列宽度
for (int columnNum = 0; columnNum <= fieldCount; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //获取当前列宽度
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) //在这一列上循环行
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(columnNum);
int length = currentCell != null?Encoding.Default.GetBytes(currentCell.ToString()).Count() : 0; //获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
}
sheet.SetColumnWidth(columnNum, (((columnWidth > 50 ? columnWidth / 4 : columnWidth) + 3) * 256));
}
#endregion
}
private void WriteXls()
{
int g = 0;
int h = 0;
int i = 0;
int j = 0;
CheckCount[0] = 0;
CheckCount[1] = 0;
////需要使用的變數。Workbook表示Excel檔，Worksheet表示一個Excel檔裡面的sheet(一個Excel檔可以有很多sheet)，Range表示Excel裡面單元格的範圍。
//Microsoft.Office.Interop.Excel.Application xlApp = null;
//Workbook wb = null;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet ws = (HSSFSheet)wb.CreateSheet(DateTime.Today.ToString("yyyyMMdd"));
//Worksheet ws = null;
//Range aRange = null;
//Range aRange2 = null;
/* object mObj_opt = System.Reflection.Missing.Value;
* //啟動Excel應用程式
* xlApp = new Microsoft.Office.Interop.Excel.Application();
* xlApp.Visible = false;
* if (xlApp == null)
* {
* Console.WriteLine("Error! xlApp");
* return;
* }
* //用Excel應用程式建立一個Excel物件，也就是Workbook。並取得Workbook中的第一個sheet。這就是我們要操作資料的地方。
* wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
* ws = (Worksheet)wb.Worksheets[1];
* if (ws == null)
* {
* Console.WriteLine("Error! ws");
* }*/
//利用Cells屬性，取得單一儲存格，並進行操作。
//ws.Name = DateTime.Today.ToString("yyyyMMdd");
string[] header = { "版本", "個案", "檔案名稱", "修改時間", "落差值(分)", "落差值(天)" };
ws.CreateRow(0);
for (int ki = 0; ki < header.Length; ki++)
{
ws.GetRow(0).CreateCell(ki).SetCellValue(header[ki]);
}
//ws.get_Range("A1", "F1").Value2 = header;
//ListFile.Clear();
//pb_main.Maximum = List_ver.Count;
//pb_main.Value = 0;
int colorcount = 0;
//string ra0 = "A2";
int ra0 = 1;
int ra1 = 0;
int last = 0;
for (int mi = 0; mi < List_ver.Count; mi++)
{
GetAllDirList(basicpath + List_ver[mi] + @"\FOR客製客戶");
//最後，呼叫SaveAs function儲存這個Excel物件到硬碟。
/* for (int i = 0; i < ListFile.Count;i++ )
* {
* ws.Cells[2+i, 1] = ListFile[i];
* }*/
//版本
//個案
//pb_file.Maximum = ListDir.Count;
//pb_file.Value = 0;
int index = 0;
foreach (KeyValuePair <string, List <string> > item in ListDir)
{
if (item.Value.Count > 0)
{
for (int p = 0; p < item.Value.Count; p++)
{
ws.CreateRow(1 + h);
//ws.Cells[2 + h, 2] = item.Key;
//ws.CreateRow(2 + h).CreateCell(2).SetCellValue(item.Key);
ws.GetRow(1 + h).CreateCell(1).SetCellValue(item.Key);
//ws.Cells[2 + h, 1] = List_ver[mi];
//ws.CreateRow(2 + h).CreateCell(1).SetCellValue(List_ver[mi]);
ws.GetRow(1 + h).CreateCell(0).SetCellValue(List_ver[mi]);
h++;
}
}
}
//修改時間
foreach (KeyValuePair <string, List <DateTime> > item in ListDate)
{
if (item.Value.Count > 0)
{
DateTime dt = item.Value[0];
for (int p = 0; p < item.Value.Count; p++)
{
//ws.Cells[2 + j, 4] = item.Value[p];
//ws.CreateRow(2 + j).CreateCell(4).SetCellValue(item.Value[p]);
DateTime dt1 = item.Value[p];
ws.GetRow(1 + j).CreateCell(3).SetCellValue(dt1.ToString());
TimeSpan Total = item.Value[p].Subtract(dt); //日期相減
//Cell cell = ws.GetRow(2 + j).GetCell(2 + j);
//ws.Cells[2 + j, 5] = Math.Round(Total.TotalMinutes,3).ToString();
int seconds = Math.Abs(Int32.Parse(Math.Round(Total.TotalSeconds, 0).ToString()));
int hours = Convert.ToInt32((seconds / 3600) - (seconds % 3600) / 3600);
seconds -= hours * 3600;
int minutes = Convert.ToInt32((seconds / 60) - (seconds % 60) / 60);;
seconds -= minutes * 60;
//ws.Cells[2 + j, 5] = string.Format("{0:00}", hours) + ":" + string.Format("{0:00}", minutes) + ":" + string.Format("{0:00}", seconds);
//ws.GetRow(2 + j).CreateCell(5).SetCellValue(string.Format("{0:00}", hours) + ":" + string.Format("{0:00}", minutes) + ":" + string.Format("{0:00}", seconds));
string edittime = string.Format("{0:00}", hours) + ":" + string.Format("{0:00}", minutes) + ":" + string.Format("{0:00}", seconds);
ws.GetRow(1 + j).CreateCell(4).SetCellValue(edittime);
//ws.Cells[2 + j, 6] = Convert.ToInt32((float.Parse(Total.TotalDays.ToString())));
//ws.GetRow(2 + j).CreateCell(6).SetCellValue(Convert.ToInt32((float.Parse(Total.TotalDays.ToString()))));
ws.GetRow(1 + j).CreateCell(5).SetCellValue(Convert.ToInt32((float.Parse(Total.TotalDays.ToString()))));
Cell cell = ws.GetRow(1 + j).GetCell(4);
if (float.Parse(Total.TotalMinutes.ToString()) > Int32.Parse(min))
{
//string palce = "E" + (2 + j).ToString();
//font_color(wb, ws.GetRow(2 + j).GetCell(2 + j),NPOI.HSSF.Util.HSSFColor.BLUE.index2);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.BLUE.index);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.BLUE.index);
//Range ras1 = ws.get_Range(palce, palce);
//ras1.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(0, 0, 255));
CheckCount[0]++;
//System.Runtime.InteropServices.Marshal.ReleaseComObject(ras1);
//ras1 = null;
}
else if ((float.Parse(Total.TotalMinutes.ToString()) < (-1) * Int32.Parse(min)))
{
//string palce = "E" + (2 + j).ToString();
//fill_background(wb, cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb, ws.GetRow(2 + j).GetCell(2 + j),NPOI.HSSF.Util.HSSFColor.RED.index);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.RED.index);
//Range ras2 = ws.get_Range(palce, palce);
//ras2.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(255, 0, 0));
CheckCount[0]++;
// System.Runtime.InteropServices.Marshal.ReleaseComObject(ras2);
// ras2 = null;
}
cell = ws.GetRow(1 + j).GetCell(5);
if (Convert.ToInt32(float.Parse(Total.TotalDays.ToString())) >= 1)
{
//string palce = "F" + (2 + j).ToString();
//Range ras3 = ws.get_Range(palce, palce);
//font_color(wb,ws.GetRow(2 + j).GetCell(2 + j), NPOI.HSSF.Util.HSSFColor.BLUE.index2);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.BLUE.index2);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.BLUE.index2);
//ras3.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(0, 0, 255));
CheckCount[1]++;
//System.Runtime.InteropServices.Marshal.ReleaseComObject(ras3);
//ras3 = null;
}
else if ((Convert.ToInt32(float.Parse(Total.TotalDays.ToString())) <= -1))
{
//string palce = "F" + (2 + j).ToString();
//Range ras4 = ws.get_Range(palce, palce);
//fill_background(wb, cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb,ws.GetRow(2 + j).GetCell(2 + j), NPOI.HSSF.Util.HSSFColor.RED.index);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.RED.index);
//ras4.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(255, 0, 0));
CheckCount[1]++;
//System.Runtime.InteropServices.Marshal.ReleaseComObject(ras4);
//ras4 = null;
}
j++;
}
}
}
//檔案
foreach (KeyValuePair <string, List <string> > item in ListDir)
{
if (item.Value.Count > 0)
{
for (int p = 0; p < item.Value.Count; p++)
{
//ws.Cells[2 + i, 3] = item.Value[p];
//ws.GetRow(2 + i).CreateCell(3).SetCellValue(item.Value[p]);
ws.GetRow(1 + i).CreateCell(2).SetCellValue(item.Value[p]);
i++;
//Updateinfo(List_ver[mi] + ".." + item.Key + ".." + item.Value[p]);
lb_info.Invoke(Setinfo, new object[] { List_ver[mi] + ".." + item.Key + ".." + item.Value[p] });
//UpdateCpuProgress(pb_file);
}
index++;
pb_file.Invoke(SetPro, new object[] { pb_file, ListDir.Count, index, lb_file });
Thread.CurrentThread.Join(100);
}
}
pb_main.Invoke(SetPro, new object[] { pb_main, List_ver.Count, mi + 1, lb_main });
//Thread.CurrentThread.Join(1);
if (mi < List_ver.Count)
{
ra1 = 1 + i;
/* ra1 = "F" + (1 + i).ToString();
* Range rax = ws.get_Range(ra0, ra1);*/
if (colorcount % 2 == 0)
{
//rax.Interior.Color =
//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("AliceBlue")); //背景顏色
for (int r1 = 0; r1 < 6; r1++)
{
for (int a2 = last; a2 < ra1; a2++)
{
fill_background(wb, ws.GetRow(a2).GetCell(r1), NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index);
}
}
}
else
{
for (int r1 = 0; r1 < 6; r1++)
{
for (int a2 = last; a2 < ra1; a2++)
{
fill_background(wb, ws.GetRow(a2).GetCell(r1), NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index);
}
}
//rax.Interior.Color =
//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("Honeydew")); //背景顏色
//ra0 = "A" + (2 + i).ToString();
//ra0 = 1 + i;
}
last = ra1;
colorcount++;
}
}
//UpdateCpuProgress(pb_main);
//string ra_row = "A1";
int ra_row = 1;
//string ra_col = "F" + (1 + j).ToString();
int ra_col = 1 + j;
//Range ra = ws.get_Range(ra_row, ra_col);
//自動調整蘭寬
//ra.Columns.AutoFit();
for (ra_col = 0; ra_col < 6; ra_col++)
{
ws.AutoSizeColumn(ra_col);
}
//ra = ws.get_Range("A1", "F1");
//ra.Interior.Color =
//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(192,192,255)); //背景顏色
for (int ai = 0; ai < 6; ai++)
{
fill_background(wb, ws.GetRow(0).GetCell(ai), NPOI.HSSF.Util.HSSFColor.ORANGE.index);
}
//置中
//ra.HorizontalAlignment = XlVAlign.xlVAlignCenter;
//ra.VerticalAlignment = XlVAlign.xlVAlignCenter;
/*
* Process[] runningProcs = Process.GetProcessesByName("excel");
* foreach (Process p in runningProcs)
* {
* p.Close();
* }*/
//string row = "A2";
int row = 2;
//string col = "F" + (1 + j).ToString();
int col = 1 + j;
//ra = ws.get_Range(row, col);
//ra.Select();
//xlApp.ActiveWindow.FreezePanes = true;
ws.CreateFreezePane(1, 0, 1, 0);
if (!isDirectory(Mydocument + @"\ModiCheck"))
{
Directory.CreateDirectory(Mydocument + @"\ModiCheck");
}
if (!isDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM")))
{
Directory.CreateDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM"));
}
if (!isDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd"));
}
filename = DateTime.Now.ToString("yyyyMMdd_HHmmss");
//wb.SaveAs(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd") + "\\" + filename + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, mObj_opt, mObj_opt, mObj_opt, mObj_opt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mObj_opt, mObj_opt, mObj_opt, mObj_opt, mObj_opt);
savefilename = Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd") + "\\" + filename + ".xls";
WriteToFile(wb, savefilename);
/* Console.WriteLine("save");
* wb.Close(false, mObj_opt, mObj_opt);
* xlApp.Workbooks.Close();
* xlApp.Quit();
* //刪除 Windows工作管理員中的Excel.exe 進程，
* System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(ra);
* //System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange);
* //System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange2);
* xlApp = null;
* wb = null;
* ws = null;
* ra = null;*/
//aRange = null;
//aRange2 = null;
//呼叫垃圾回收
GC.Collect();
this.Invoke(closewin, new object[] { this });
}
public override void AutoSizeColumn(int column, string worksheetName)
{
HSSFSheet tempWS = GetWorksheet(worksheetName);
tempWS.AutoSizeColumn(column);
}